제 1 장 : SQL 서버란?

출처: http://blog.daum.net/luckyman717

1. SQL Server란?

SQL Server가 무엇인지 정의하기 전에 우선 서버(Server)와 클라이언트(Client)의 역할을 살펴보겠습니다. 서버와 클라이언트의 관계를 정확히 이해한다면 SQL Server의 역할을 쉽게 이해 할 수 있기 때문입니다.

※ 구조화 조회 언어 [ 構造化照會言語 structured query language(SQL) ]

관계형 데이터베이스의 조작과 관리에 사용되는 데이터베이스 하부 언어(sublanguage).

데이터베이스의 모든 속성과 성질, 예를 들면 레코드 설계, 필드(field) 정의,파일 위치 등을 정의하는 데이터 정의어(DDL)와 데이터베이스 내의 데이터를 검색, 삽입, 갱신, 삭제하는 데 사용되는 데이터 조작 처리 언어(DML)로 구성되어 있다.

현재 대부분의 관계형 데이터베이스 관리에 구조화 조회 언어(SQL)가 채택되고 있다.

1970년대 미국 IBM사가 개발하였는데 그 후에 많은 표준화 기관에서 표준화하였다.

IBM은 structured querry language의 약자로서 SQL이라고 하였으나, 국제 표준화 기구(ISO)에서는 약어가 아닌 SQL이라는 고유 명사를 사용하여 국제 표준화하고 있다.

1987년에 ISO 9075-1987로 국제 표준을 제정하였는데,1987년에 개정판 ISO 9075-1987을 제정하고 1992년에 다시 기능 확장판인 ISO 9075-1992를 제정하였다.

이것을 SQL 2라고 부른다.

1) Server

서버가 무엇인지 이야기 할 때 우리는 제일 먼저 '서비스(Service)'를 떠올릴 수 있어야 합니다. 서버는 클라이언트로부터의 요청을 받아 그 요청에 대하여 응답해주는 역할을 담당합니다. 즉, 클라이언트가 서비스를 요청하면 서버는 그 요청을 받아 해석하고 수행해서 그것에 합당한 결과를 클라이언트에게 전달합니다. 메일서비스를 담당하는 메일서버, 웹서비스를 담당하는 웹서버, 데이터베이스 서비스를 제공하는 데이터베이스서버 등등 여러 가지 서버를 주위에서 볼 수 있습니다. 이러한 서버들은 Windows NT나 Windows 2000 Server와 같은 운영체제에 탑재되어 운영됩니다.

2) Client

서버에게 필요한 서비스를 요청하는 역할을 담당합니다. 예를 들면 메일 클라이언트는 메일서버에게 메일 발송을 의뢰하거나 메일서버로부터 메일을 전달받습니다. 대부분 Win 9X나 Windows 2000 Me, Windows 2000 Professional이 클라이언트용으로 사용되며 특별한 경우 서버도 클라이언트 역할을 할 수 있습니다.

3) SQL Server

위에서 설명된 서버와 클라이언트 역할을 가지고 SQL Server를 정의한다면 클라이언트로부터 요청(쿼리문)을 받아 그 결과을 클라이언트에게 전달해주는 데이터베이스 관리시스템(DBMS)이라고 할 수 있습니다. 특히 SQL Server는 데이터베이스관리시스템 중에서도 관계형데이터베이스(RDB)를 관리하는 관계형데이터베이스관리시스템(RDBMS) 입니다.

※ RDBMS : Relational Database Management System

SQL Server는 데이터베이스를 저장하고 있으면서 클라이언트의 데이터 검색 및 수정 삭제에 대한 요청을 받아 처리하고 그 결과를 클라이언트에게 전달합니다. 이때 클라이언트의 요청은 대부분 나중에 배우게 될 Transact-SQL(앞으로는 T-SQL로 표기하겠습니다.) 문에 의해 이루어집니다.

2. SQL Server Service

RDBMS의 역할을 수행하기 위해 SQL Server는 여러가지 서비스들을 포함하고 있습니다. 이들은 SQL Server가 설치되고 나면 서비스로서 등록되어 수행 됩니다. 각 서비스들의 수행 여부는 다음과 같이 SQL Server 서비스 관리자를 통해서 제어 할 수 있습니다.

각 서비스들의 역할은 다음과 같습니다.

1) SQLServer Service

데이터베이스의 무결정을 유지하면서 데이터베이스를 관리하는 서비스로 클라이언트가 요청하는 T-SQL처리를 담당합니다. 가장 기본이되는 서비스이므로 SQL Server가 설치되면 항상 이 서비스는 자동으로 수행되어집니다. 만일 이 서비스가 정지하면 SQL Server는 대부분의 서비스를 할 수 없으며 클라이언트는 접속도 할 수 없게 됩니다.

2) SQLServer Agent Service

관리자는 SQL Server를 관리하기 위해서 많은 부분을 자동화 할 수 있습니다. 예를 들면 자정이 되면 데이터베이스 백업을 받고 관리자에게 메일로 통보하도록 설정을 한다거나, 로그가 꽉 차면 관리자에게 통보하고 자동으로 로그를 백업 받고 지우도록 스케줄을 설정할 수가 있는데 이를 가능하게 하는 것이 Agent Service 입니다. SQL Server가 설치되면 기본적으로 이 서비스는 자동수행이 되지 않습니다. 필요한 경우 관리자는 이 서비스를 자동 수행되도록 설정해야 합니다.

3) MS DTC

MS DTC(Distributed Transaction Coordinator)는 하나의 트랜잭션에 대하여 여러 개의 서버가 같이 처리되어야 하는 경우 하나라도 오류가 발생하면 다른 서버에도 반영이 되지 않도록 할 때, 즉 전부다 반영되든지 아니면 아예 아무것도 반영이 안 되든지 하도록 할 때 사용됩니다.

4) MS Search

문자형 데이터에 대하여 Full-Text 검색이 가능하도록 해주는 서비스입니다. 나중에 설명할 기회가 있겠지만 이 서비스를 이용하면 문자열을 검색할 때 사용하는 Like보다 막강하게 검색을 할 수 있습니다.

제 2 장 : 운영체계와 SQL Server와 연관성

1. 운영체제와 SQL Server의 관계

SQL Server는 다양한 운영체제 플랫폼에서 수행 될 수 있는 Server Component와 Client Component를 가지고 있습니다.

1) Server Component

다음의 플랫폼에 SQL Server Component가 설치되어 수행될 수 있습니다.

o Windows 2000

o Windows NT

o Windows Me

o Windows 98

o Windows CE

Windows 2000과 Windows NT에서는 서비스로서 수행이 되며, 그 이외의 운영체제에서는 응용프로그램으로서 작동됩니다. 또한 Windows 2000 Professional, Windows Me, Windows 98에는 Personal Edition만 설치 할 수 있습니다. SQL Server 에는 Enterprise Edition, Standard Edition, Personal Edition, Developer Edition 등이 있습니다.

아직은 본적이 없지만 Windows CE가 운영되는 PDA에 SQL Server를 설치해서 사용할 수 있다고 합니다.

2) Client Component

다음의 플랫폼에 SQL Client Component가 설치되어 수행될 수 있습니다.

o Windows 2000

o Windows NT

o Windows Me

o Windows 98, 95

o Third Party

o Internet Browser

Windows 9X만 클라이언트가 되라는 법은 없습니다. 원한다면 Windows 2000 Advanced Server도 클라이언트로서 다른 서버에게 서비스를 요청할 수 있습니다. SQL Server 2000에서는 XML이 지원되어 인터넷브라우저에서 URL에 XML문장을 입력하여 쿼리할 수 있습니다.

SQL Server의 XML 지원은 SQL Server 2000버전의 새로운 기능 중에 주목할 만한 사항입니다. 인터넷 시대에 발맞추기 위한 기능 향상인 것 같습니다.

2. Windows 2000과 SQL Server의 관계

SQL Server는 Windows 2000의 여러 가지 특성을 그대로 이용할 수 있습니다. 몇 가지 대표적인 내용만 살펴보도록 하겠습니다.

1) Active Directory

SQL Server를 Active Directory에 등록하여 사용자들이 Active Directory 검색을 이용하여 찾을 수 있도록 할 수 있습니다. Active Directory가 무엇인지 궁금하시면 Windows 2000을 공부해 보시기 바랍니다.

2) 보안(Security)

Windows 2000의 보안기능을 사용하여 SQL Server의 보안을 강화 시킬 수 있습니다. SQL Server 자체에도 보안을 위한 여러가지가 제공됩니다. 하지만 Windows 2000과 연관지어 운영하면 더욱 강력한 보안을 유지 할 수 있습니다.

3) 다중 프로세서 지원(Multiprocessor Support)

Windows 2000의 경우 다음과 같이 여러개의 프로세서를 사용할 수 있습니다.

o 2000 Data Center : 32개

o 2000 Advanced Server : 8개

o 2000 Server : 4개

o 2000 Professional : 2개

o NT 4.0 Server Enterprise Edition : 8개

o NT 4.0 Server : 4개

이에 대하여 SQL Server는 다음과 같이 여러개의 프로세서를 사용할 수 있습니다.

o Enterprise Edition : 32개

o Standard Edition : 4개

o Personal Edition : 2개

예를 들면 Windows 2000 Advanced Server가 8개의 프로세서를 지원하지만 여기에 SQL Standard Edition을 설치하게되면 SQL Server는 4개의 프로세서만 사용하게 됩니다. 그러므로 설치된 운영체제에 맞는 SQL Server 를 설치하여야 합니다.

프로세서가 하나인 서버에서 SQL Server를 운영하는 것은 적당하지 않다고 생각합니다. SQL Server가 제 기능을 발휘하려면 프로세서가 2개 이상은 되어야 한다고 생각합니다.

4) 이벤트 뷰(Event View)

SQL Server는 자신의 메시지를 Windows 2000의 이벤트에 기록합니다. 그러므로 관리자는 Windows 2000의 Event Viewer를 통하여 SQL Server를 모니터링 할 수 있습니다.

5) 클러스터링(Clustering)

하나의 서버에 문제가 발생하여 제 기능을 발휘하지 못할 때 다른 서버가 그 역할을 수행하도록 하는 것이 클러스터링 기능입니다. 이러한 Windows 2000의 클러스터링 기능을 이용하면 하나의 SQL Server에 문제가 발생했을 때 다른 SQL Server가 그 역할을 대신하도록 할 수 있습니다. 물론 클러스터링을 구현하기 위해서는 많은 비용이 소요됩니다. 그러므로 큰 규모의 데이터베이스을 운영하는 경우 클러스터링을 구현하는게 합당하다고 생각합니다.

6) 시스템 모니터(System Monitor)

SQL Server를 설치하면 새로운 오브젝트와 카운터가 Windows Monitor에 전달됩니다. 이를 이용하여 성능향상을 위한 모니터링을 할 수 있습니다.

제 3 장 : 데이터베이스의 종류

데이터베이스란 정보를 저장하는 창고라고 생각하시면 됩니다. 우리들의 중요한 정보가 데이터베이스에 기록이 되는 것입니다. SQL Server는 이러한 데이터베이스를 관리해주는 데이터베이스 관리 시스템인 것입니다. 이 장에서는 데이터베이스의 종류에 대하여 살펴보도록 하겠습니다.

1. 데이터베이스의 분류

1) 시스템 데이터베이스

Master, Model, Tempdb, Msdb, Distribution 데이터베이스를 시스템 데이터베이스라고 합니다. 이들 데이터베이스는 SQL Server가 설치되면 자동적으로 생성됩니다.(Distribution 데이터베이스는 예외) 시스템 데이터베이스는 SQL Server에 대한 정보를 가지고 있으며, SQL Server가 데이터베이스를 관리하기 위해서 사용됩니다. 그러므로 사용자는 시스템 데이터베이스를 함부로 수정해서는 안됩니다.

Distribution 데이터베이스는 SQL Server를 처음 인스톨 할 때는 만들어지지 않고 Replication 이 설정될 때 만들어져 배포자(Publisher)와 구독자(Subscriber) 사이에서 복제할 내용을 전달하는 역할을 수행합니다. 이들 시스템 데이터베이스 각각의 역할은 잠시후에 살펴보도록 하겠습니다.

2) 예제 데이터베이스

Pubs, Northwind 데이터베이스가 예제 데이터베이스입니다. 이 두개의 데이터베이스는 사용자가 SQL Server를 공부할 때 사용 할 수 있는 샘플이 되는 데이터베이스입니다. 제가 강좌를 진행하면서도 이 두 데이터베이스를 많이 이용하게 될 것입니다.

3) 사용자 데이터베이스

이외에 사용자가 만든 데이터베이스를 사용자 데이터베이스라고 합니다. 어떻게 보면 가장 중요한 데이터베이스라고 할 수 있습니다. 이렇게 SQL Server를 공부 하는 것도 이 사용자 데이터베이스를 효율적으로 관리하고 사용하는 방법을 배우기 위한 것이라고 생각합니다.

2. 시스템 데이터베이스

다음은 EM(Enterprose Manager)에서 본 시스템 데이터베이스와 예제 데이터베이스 입니다.

1) Master 데이터베이스

여러 가지의 데이터베이스가 SQL Server에 만들어질 수 있습니다. 그렇다면 이러한 데이터베이스를 대표하여 관리해 줄 별도의 데이터베이스가 필요합니다. 이것이 바로 Master 데이터베이스입니다. Master 데이터베이스는 SQL Server의 여러 가지 환경 정보와 로그인 정보, 전체 데이터베이스에 대한 정보를 갖고 있습니다. 그러므로 가장 중요한 System 데이터베이스라고 할 수 있습니다.

2) Model 데이터베이스

데이터베이스의 이름에서 그 역할을 추측 할 수 있습니다. 사용자 데이터베이스를 만들 때(CREATE DATABASE) 이 Model 데이터베이스가 복사가 되어 만들어 집니다. 이런 이유로 Model 데이터베이스의 설정을 변경하면 이후에 만들어지면 사용자 데이터베이스에 그 내용이 반영되어 일관성 있는 사용자 데이터베이스 생성을 가능하게 합니다. 예를 들어 앞으로 만들어질 사용자 데이터베이스에 특정 계정을 만들고 싶을 때 이 계정을 Model 데이터베이스에 만들어 두면 별도의 작업이 없이도 새로 만들어지는 데이터베이스에 이 계정이 포함되게 됩니다.

3) Tempdb 데이터베이스

SQL Server가 운영될 때 만들어지는 임시테이블들이 생성되는 곳입니다. Tempdb는 SQL Server가 재시작 될 때마다 초기화 되므로 사용자 테이블을 Tempdb에 만드는 것은 바람직하지 않습니다. 가끔 Tempdb에 테이블을 만들어 두고나서 나중에 자동으로 지워진 후 누가 지웠냐고 난리법석을 떠는 것을 본적이 있습니다. 우리는 이런 실수를 해서는 안 되겠습니다. 물론 테스트를 위한 임시 테이블은 이곳에 만들어도 됩니다. 저도 테스트용 테이블을 만들어 확인 할 때 가끔 사용하게 됩니다.

4) Msdb 데이터베이스

Job에 대한 정보를 갖습니다. Alert 및 Operator에 대한 정보를 관리하게 되며, DTS에 대한 정보도 Msdb에 기록됩니다. SQL Server 관련 서비스 중에서 MSSQLServer Agent 서비스가 대부분 사용하는 데이터베이스입니다.

3. 예제 데이터베이스

SQL Server가 설치되면 연습용 데이터베이스 두개가 만들어집니다.

o Pubs

o Northwind

이 두 데이터베이스는 SQL Server를 공부할 때 유용하게 사용될 수 있으며 실제로 여러 교재에서 예제로 사용됩니다. 만일 이 두 데이터베이스에 문제가 생기면 SQL Server 폴더의 Install 폴더에 있는 Instpubs.sql, Instnwnd.sql 스크립트를 수행하여 언제든지 재생성 할 수 있습니다.

데이터베이스에 대해서는 참 배울게 많은 것 같습니다. 이번 장에서 배울 데이터베이스의 객체는 내용은 많지만 별로 부담 없이 들어도 되는 것입니다. 왜냐하면 앞으로의 강좌들이 이들 객체들에 대한 설명이 될 것이기 때문입니다.

제 4 장 : 데이터베이스의 객체

1. 데이터베이스 객체란?

데이터베이스에 저장되는 것들은 일반적으로 잘 아는 테이블 외에 기타 여러 가지 것들이 저장됩니다. 이것을 데이터베이스 객체(Database Objects)라고 부릅니다. 물론 이러한 객체들이 데이터베이스를 사용 할 때 필수적이지는 않습니다. 하지만 이러한 객체들을 제대로 이용한다면 많은 편리함을 얻을 수 있습니다.

2. 데이터베이스 객체의 종류

정말 많은 객체들이 존재합니다. 아래에 나열된 객체들에 대해서는 나중에 자세하게 살펴볼 계획입니다. SQL Server에 대한 공부의 대부분이 아래 나열된 객체를 이해하는데 할애 된다고 봐도 과언이 아닙니다. 여기서는 이런 것들이 있구나...라고 이해하셔도 무방합니다.

1) Table

하나 또는 여러 컬럼(Column)들이 모여 하나의 레코드를 이룹니다. 이러한 레코드들이 모여 테이블이 됩니다. 예를 들어 [사원] 테이블은 사번, 이름, 부서 등 여러 컬럼을 갖게 되며 사원수만큼의 레코드를 갖게 됩니다.

테이블은 시스템테이블과 사용자테이블로 구분 할 수 있습니다. 시스템테이블의 경우는 대부분 SQL Server가 내부적으로 관리를 목적으로 사용되어집니다. 물론 이들 시스템테이블을 참조하면 여러가지 정보를 얻을 수 있지만 시스템테이블을 직접 참조하는 것은 권장되지 않는 방법입니다. 대신 시스템 저장프로시저나 스키마뷰를 이용해서 이러한 정보를 참조할 수 있습니다. 이제 대해서는 나중에 자세히 살펴보도록 하겠습니다.

2) Index

쿼리문을 이용하여 정보를 검색할 때 빠른 수행을 위해서 인덱스를 구성합니다. 물론 빠른 검색 목적 이외에도 데이터의 무결성을 위해서도 사용됩니다. 인덱스에는 Clustered 인덱스와 Non-Clustered 인덱스가 있습니다. 너무나 중요한 개념이므로 나중에 많은 시간을 할애해서 설명할 예정입니다.

3) View

뷰는 실제로는 존재하지 않는 가상의 논리적인 테이블입니다. 실제 테이블을 기준으로 사용자 입장에서 논리적인 테이블을 만들어 쉽게 접근 할 수 있게 합니다. 뷰는 사용의 편리성과 보안성을 제공해 줍니다.

4) Stored Procedure

복잡한(단순해도 상관없습니다) 쿼리문을 저장해 두었다가 손쉽게 재사용 할 수 있는 저장 프로시저입니다. 저장 프로시저가 만들어질 때(CREATE PROCEDURE) 소스가 미리 컴파일 되어 있고 수행 계획이 처음 수행될 때만 한번 만들어지고 이후에는 이러한 과정이 생략되므로 일반적인 쿼리수행에 비해 빠른 성능을 제공합니다.

5) Trigger

테이블의 값이 변경(INSERT, UPDATE, DELETE)될 때 자동적으로 수행되는 저장 프로시저의 하나입니다.

6) Data Type

SQL Server가 제공하는 기본적인 데이터 형식말고도 사용자가 임으로 정할 수 있는 데이터 형식을 가리킵니다. 예를 들어 사원번호를 선언하는데 있어 어느 개발자는 CHAR(05)로, 어느 개발자는 CHAR(10) 또는 VARCHAR(10)과 같이 서로 다르게 사용할 수 있는데, 이를 해결하기 위하여 SABUN_TYPE 이라는 데이터 형식을 만들어 CHAR(05)로 선언하면 개발자들은 사원번호의 데이터 형식을 SABUN_TYPE 이라고 지정해서 사용하면 됩니다.

7) Default

데이터가 입력될 때 값이 들어오지 않으면 자동으로 입력되도록 Default 를 지정할 수 있습니다. 만일 주소 칼럼에 데이터를 저장하는데 (unknown) 을 Deault로 하면주소가 입력되지 않으면 (unknown)이 자동으로 입력됩니다.

8) Rule

특정 칼럼에 대하여 저장될 수 있는 값을 지정할 때 사용됩니다.

9) Constraint

데이터의 무결성을 위해서 제약을 주는 것입니다. Primary Key, Foreign Key, Check 등이 있습니다.

10) User Defiend Function

사용자 정의 함수는 SQL Server 2000에서 새롭게 선보이는 기능입니다. 특정한 값은 물론 테이블까지 사용자 정의 함수의 결과값이 될 수 있어 매우 유용하게 사용됩니다.

3. 데이터베이스 객체 사용하기

데이터베이스 객체를 지정하기 위해서는 다음의 형식을 따라야 합니다.

서버명.데이터베이스명.소유자.객체명

예를 들어 NTSRV01.Northwind.dbo.Orders 와 같이 사용합니다. 객체명을 제외한 앞 세부분은 생락이 가능한데 이 경우 다음과 같은 의미를 갖게 됩니다.

o 서버명을 생략하면 현재 로그인한 SQL Server명이 기본값으로 사용됩니다.

o 데이터베이스명을 생략하면 현재 사용 중인 데이터베이스를 기본값으로 사용됩니다.

o 소유자가 생락되면 현재 연결한 사용자 계정이 기본값으로 사용됩니다.

이런 이유로 실제 개발시 많은 부분이 생략되어 사용됩니다. 하지만 나중에 스크립트를 볼 때 쉽게 파악이 가능하도록 되도록이면 붙여주는데 좋다고 생각합니다. 가끔은 자기가 작성한 스크립트를 자신도 이해하지 못하는 경우가 발생합니다.

외국 회사로부터 컨설팅을 받는 가운데 그들이 작성한 소스를 보면 경악을 금치 못합니다. 소스 구석구석의 너무 과하다고 생각될 정도의 코멘트 때문입니다. 본받을 만한 습관이라고 생각합니다.

제 5 장 : Transact-SQL 이란

1. Transact-SQL이란?

보통은 줄여서 T-SQL 이라고 표기합니다. SQL Server의 SQL 구문은 ANSI와 ISO에서 정한 ANSI SQL 표준의 최신 버전인 ANSI SQL-92 의 기준을 따르고 있습니다. 하지만 새로운 기능 수행을 위해 MS사는 새로운 SQL 구문을 추가 하였는데 이를 Transact-SQL 이라고 부릅니다. 이런 이유로 Transact-SQL 구문은 다른 데이터베이스 환경에서 작동되지 않을 확률이 높습니다. 그래서 되도록 ANSI SQL 구문으로 작성이 가능한 것을 Transact-SQL 구문으로 작성하는 것은 바람직하지 않다고 생각합니다.

※ ANSI : American National Standards Institute / ISO : International Standards Organization

2. SQL 구문의 종류

SQL 구문은 그 기능에 따라 다음과 같이 세 가지로 크게 분류 할 수 있습니다.

o DCL(Data Control Language)

o DDL(Data Definition Language)

o DML(Data Manipulation Language)

이 세 가지 분류를 필히 기억할 필요는 없지만 종종 언급이 되는 교재들이 있으므로 알고 있으면 도움이 될 것 같습니다. Transact-SQL문에 대한 자세한 사용 방법은 앞으로 많은 시간을 할애하여 설명할 예정입니다.

1) DCL(Data Control Language)

데이터베이스 사용자와 Role에 대하여 권한을 지정하거나 제거할 사용하는 SQL 구문을 가리킵니다.

※ Role : SQL Server 7.0에서 새로 등장했으며, 그룹의 의미와 권한들의 집합의 의미를 가지고 있습니다. Windows NT나 Windows 2000에서 그룹을 정하여 권한을 할당하고 이 그룹에 사용자 계정을 포함하여 관리함으로써 관리의 용이성을 기한 것처럼 SQL Server에서는 Role을 사용 할 수 있습니다.

권한을 설정하는 SQL 구문에는 다음과 같이 GRANT, DENY, REVOKE 세 가지가 있습니다.

GRANT	사용자 계정이나 Role에 대하여 데이터에 대한 접근 권한이나 Transact-SQL 사용 권한을 부여 할 때 사용합니다.
DENY	GRANT와 반대로 데이터에 접근하지 못하게 하거나 Transact-SQL을 수행 하지 못하도록 거부할 때 사용합니다.
REVOKE	이미 GRANT 또는 DENY로 설정된 권한을 제거(Remove) 할 때 사용합니다.

다음의 예에서는 Jangrae라는 사용자 계정에 Pubs 데이터베이스의 Titles 테이블을 SELECT 할 수 있는 권한을 부여하고 있습니다.

USE Pubs

GRANT SELECT ON Titles TO Jangrae

SQL Server에서는 관리의 편의를 위하여 많은 도구들이 제공되고 있습니다. 이러한 도구들을 이용하여 굳이 SQL 문을 사용하지 않더라고 대부분의 작업을 할 수 있습니다. 예를 들어 위 예의 경우 권한을 설정하는 것은 EM(Enterprise Manager)를 이용하여 쉽게 작업을 할 수 있습니다. 하지만 배우는 과정에서는 되도록 SQL문을 직접 작성해서 수행해 보는 것이 많은 도움이 될거라 생각합니다. SQL을 통하여 작업하는 것이 숙달이 되면 시간 절약을 위해서 EM을 사용하는 것이 현명하다고 생각합니다. 처음부터 EM을 사용하여 작업을 하게 되면 수행되는 작업에 대한 내부적인 처리 내용을 정확하게 이해 할 수 없게 되며, 만일 EM에 문제가 발생하면 SQL 구분을 모르면 아무 작업도 할 수 없게 될 수도 있기 때문입니다.

2) DDL(Data Definition Language)

CREATE DATABASE 와 같이 데이터베이스, 테이블 또는 기타 오브젝트를 생성할 때 사용되는 SQL 구문으로 다음을 포함합니다.

o CREATE

o ALTER

o DROP

다음의 예에서는 MyDB 데이터베이스에 Friend 테이블을 생성하고 있습니다.

USE MyDB

CREATE TABLE Friend

(Name varchar(20), Tel char(12),Address varchar(50))

CREATE를 통해서 만들어진 것은 ALTER를 통해서 변경이 가능하며, DROP을 통해서 삭제 할 수 있습니다. 그래서 CREATE관련된 내용을 배울 때ALTER와 DROP 구문을 항상 같이 배우게 됩니다.

3) DML(Data Manipulation Language)

데이터를 검색하거나 수정 및 삭제 할 수 있는 SQL 구문으로 앞으로 가장 많이 사용하게 될 SQL 문이라 할 수 있으며 다음을 포함합니다.

o SELECT

o INSERT

o UPDATE

o DELETE

다음의 예에서는 Pubs 데이터베이스의 Titles 테이블의 모든 내용을 검색하고 있습니다.

USE Pubs

SELECT * FROM Titles

제 6 장 : 데이터베이스의 구조

SQL Server 7.0 이전의 버전에서는 데이터베이스를 만드는 과정과 관리하는 방법이 상당히 까다로웠습니다. 우선 디바이스를 만들고 그 안에 데이터베이스를 만들어야 했습니다. 그리고 필요에 따라 적당한 사이즈로 늘려 주어야 했으며, 한번 늘린 사이즈는 다시 줄이기가 너무 힘들었습니다. 하지만 SQL 7.0 버전부터는 그렇지 않습니다. 곧바로 원하는 사이즈로 데이터베이스를 만들 수 있으며, 사이즈는 자동으로 필요한 때에 증가가 되도록 할 수 있었으며 필요한 경우 사이즈를 줄일 수도 있습니다.

이제 곧 우리는 데이터베이스를 만들어보게 됩니다. 그 전에 데이터베이스가 무엇인지, 그리고 어떻게 구성이 되어 있는지 살펴보도록 하겠습니다.

1. 데이터베이스란?

데이터베이스란 무엇이가를 저장해 두는 곳이라고 우선 정의할 수 있습니다. 이때 무엇인가에 포함되는 것은 테이블(Table), 뷰(View), 인덱스(Index), 저장 프로시저(Stored Procedure), 트리거(Trigger)와 같은 객체(Object)들입니다. 데이터베이스의 객체에 대해서는 예전에 간략하게 설명한 적이 있습니다. 머지않아 각각의 객체들에 대해서 엄청 많이 이야기하게 될 것 같습니다.

2. 데이터베이스를 구성하는 파일

SQL Server의 데이터베이스는 운영체제 상에서 보면 확장자가 mdf(또는 ndf)와 ldf 인 파일로 존재하게 됩니다. 예를 들어 연습용 데이터베이스인 pubs 데이터베이스의 경우는 pubs.mdf와 pubs_log.ldf 두개의 파일이 존재합니다.

데이터베이스는 데이터와 로그로 분리되어 저장됩니다. 데이터의 경우는 mdf 확장자(또는 ndf)를 갖으며, 로그의 경우는 ldf 확장자를 갖고 저장됩니다. 확장자(mdf, ndf, ldf)는 반드시 정해진 것이 아니며 원하는 경우 바꿀 수가 있지만 되도록 그대로 사용하는 것이 바람직합니다. 확장자를 봄으로써 어떤 역할을 하는 파일인지 구분을 할 수 있기 때문입니다. 만일 pubs.mdf를 pubs.doc 으로 한다면 워드파일이지 데이터 파일이라고 생각 할 수 없기 때문입니다.(만든 사람만 알 수 있겠지요)

다음의 그림은 데이터베이스의 구조를 간단히 보여주고 있습니다.

1) 데이터 파일(*.mdf, *.ndf)

실제 자료를 저장합니다. 기본적으로 데이터 파일은 하나만 있어도 되지만 필요하면 더 만들 수 있습니다. 처음 만들어진 파일이 mdf 확장자를 갖게 되며 추가되는 파일은 확장자가 ndf 가 됩니다.

2) 로그 파일(*.ldf)

로그는 트랜잭션 처리와 시스템에 문제가 발생했을 때 복구를 위해 사용됩니다. 보통 데이터 파일의 25% 정도의 사이즈를 갖습니다.

3. Page와 Extents

저는 계산하는 것을 참 싫어합니다. 하지만 여기서는 약간의 계산을 해보고자 합니다.

1) Page

데이터는 8KB 단위의 블럭을 단위로 저장되는데 이를 페이지(Page)라고 합니다. 결과적으로 데이터베이스는 1MB에는 128Page를 저장 할 수 있게 됩니다. 하나의 Row는 여러 개의 Page에 걸쳐 저장 될 수 없고 단 하나의 Page에만 저장 될 수 있습니다. 이 사실을 통해 하나의 Row(데이터 레코드로 생각하셔도 됩니다.)의 최대 사이즈는 8060 바이트입니다. 계산해보면 8 X 1024 는 8192 입니다. 하지만 8192바이트를 Row가 전부 차지하지 못하는 이유는 몇 가지 정보를 저장하기 위한 약간의 공간(8192 - 8060 = 132)이 필요하기 때문입니다. 간단히 설명한다면 페이지 헤더가 이 공간중에 96KB를 사용하게 되고 나머지는 각 Row가 저장되고 관리되기 위한 몇몇 정보를 기록하는데 사용됩니다.

2) Extent

테이블과 인덱스는 Extent에 저장됩니다. Extent란 8개의 연속된 페이지를 가리킵니다. 1 Page가 8KB 이므로 1 Extent는 64KB(8 KB X 8) 입니다.

제 7 장 : 데이터베이스 생성

지금까지는 개념을 이해하기 위한 이론에 치우친 강좌였습니다. 하지만 이제부터는 직접 무엇인가를 해보면서 결과를 볼 수 있는 강좌가 이루어지게 됩니다. 중간 중간에 기록된 [예제]를 꼭 실습해 가면서 모든 내용을 자신의 것으로 만들어 보시기 바랍니다. 그냥 눈으로 보면서 "아..그렇게 되겠구나." 라고 추측만하고 지나가시면 금방 잊어버리게 됩니다. SQL Server를 처음으로 접하시는 분 중에 실습을 할 환경이 갖추어지신 분들은 꼭 실습을 하시면서 공부를 하시기 바랍니다. 실제 업무를 대신 할 수 있는 것은 실습뿐이기 때문입니다.

자, 실습할 준비가 되셨나요?

1. 데이터베이스 생성 원리

CREATE DATABASE 문을 이용하여 데이터베이스를 만들게 되면 SQL Server는 Model 데이터베이스를 복사함으로써 만들려고 하는 데이터베이스와 이와 관련된 메타데이터(데이터베이스에 대한 정보)를 초기화합니다. 이러한 Model 데이터베이스의 특성을 이용해서 특정한 테이블(Table), 뷰(View), 저장 프로시저(Stored Procedure), 데이터 형식(Data Type)등을 Model 데이터베이스에 미리 만들어 놓으면 새로 만들어지는 모든 데이터베이스에 이러한 객체들이 포함되게 할 수 있습니다.

[그림 1]

2. 데이터베이스 만들기

SQL Server에서 데이터베이스를 만드는 방법은 다음의 세 가지가 있습니다.

o EM(Enterprise Manager) 사용

o 마법사 사용

o 쿼리문(CREATE DATABASE) 사용

우선 쿼리문을 이용해서 공부를 하시고 익숙해지면 EM을 사용하시기를 권합니다. 가끔은 EM을 사용할 수 없는 상황에서 작업을 해야하는 경우가 있기 때문입니다. 마법사(Wizard)의 경우는 아직 잘 모를 때 빨리 특정 작업을 수행하고자 하는 경우에(하지만 꼭 마법사를 이용해야 하는 경우도 있습니다.) 사용하시기를 권합니다.

1) EM(Enterprise Manager) 이용한 데이터베이스 생성

[그림 2] 처럼 해당 SQL Server에서 마우스 오른쪽 버튼을 눌러 [새로만들기(N)] 에서 [데이터베이스(D)...]을 선택하시면 [그림 3] 처럼 데이터베이스를 만드는 화면이 표시됩니다.

[그림 2]

[그림 3]

위 [그림 3]의 경우는 Sample 데이터베이스를 만드는 화면에서 3가지 탭의 화면을 한번에 보여주고 있습니다. 각 항목에 대한 자세한 설명은 쿼리문을 이용한 데이터베이스 생성방법에서 설명되어집니다.

2) CREATE DATABASE문을 이용한 데이터베이스 생성

드디어 처음으로 쿼리문을 배우게 되었습니다. CREATE DATABASE는 데이터베이스의 이름과 사이즈 및 파일을 지정하여 데이터베이스를 만드는 구문입니다. 자세한 문법을 알고자 한다면 Books Online을 참고해 주시기 바랍니다. 여기서는 예를 들어서 설명하도록 하겠습니다.

[예제1]

다음의 예제는 MyDB를 만드는 예입니다. 우선 다음 항목들을 이해하시고 예제를 보시기바랍니다.

o NAME : 내부적으로 사용될 데이터베이스 파일의 놀리적인 이름

o FILENAME : 데이터베이스 파일의 물리적인 위치와 파일명

o SIZE : 데이터베이스의 초기 사이즈

o MAXSIZE : 데이터베이스가 증가될 수 있는 최대 사이즈, 지정되지 않으면 하드디스크가 허용하는한 무작정 사이즈 증가.

o FILEGROWTH : 데이터베이스 공간이 부족할 떄 자동으로 증가하게되는 사이즈

USE master

GO

CREATE DATABASE MyDB

ON

(NAME = MyDB_Data,

FILENAME = 'D:\Data\MyDB.mdf',

SIZE = 10MB,

MAXSIZE = 40MB,

FILEGROWTH = 5MB )

LOG ON

(NAME = MyDB_Log,

FILENAME = 'D:\Data\MyDB.ldf',

SIZE = 5MB,

MAXSIZE = 25MB,

FILEGROWTH = 5MB)

GO

[설명]

o 우선 D:\Data 폴더가 존재하고 있어야 합니다. 그렇지 않으면 오류가 발생합니다.

o 실제 OS상에서 데이터는 MyDB.mdf로 로그는 MyDB.ldf로 D:\Data 폴더에 저장합니다.

o Mydb.mdf와 MyDB.ldf는 SQL Server 내에서는 MyDB_Data와 MyDB_Log라는 이름으로 관리됩니다.

o 만들어지는 데이터베이스의 사이즈는 10MB이며, 40MB까지 5MB씩 증가 할 수 있습니다.

o 로그의 경우는 처음 5MB 사이즈로 만들어지고 25MB까지 5MB씩 증가 할 수 있습니다.

o 위에서 제시한 MAXSIZE데 도달하면 더이상 데이터 기록이 불가능합니다. MAXSIZE를 지정하지 않으면 디스크가 허용하는 한 무작정 증가합니다.

데이터베이스 생성에 대한 예제를 두개 더 들어보고자 합니다. 앞에서의 예제와 비교해서 달라지는 부분을 자세히 살펴보시기 바랍니다.

[예제2]

USE master

GO

CREATE DATABASE Products

ON

(NAME = Products_Data,

FILENAME = 'D:\data\products.mdf',

SIZE = 4MB,

MAXSIZE = 10MB,

FILEGROWTH = 1MB)

GO

[설명]

o 실제 OS상에서 데이터는 products.mdf로 폴더에 저장합니다.

o 로그가 지정되지 않았으므로 자동으로 SQL Server는 D:\Data 폴더에 products_log.ldf 로그 파일을 만듭니다.

o products.mdf는 SQL Server 내에서는 products_Data로 관리되며 products_log.ldf는 products_Log라는 이름으로 관리됩니다.

o 만들어지는 데이터의 사이즈는 4MB이며, 10MB까지 1MB씩 증가 할 수 있습니다.

o 로그는 지정되지 않았으므로 자동으로 처음 1MB정도(데이터베이스의 25%)사이즈로 만들어지고 필요한 경우 하드디스크가 허용하는한 10% 씩 증가하게 됩니다.

[예제3]

USE master

GO

CREATE DATABASE Products2

GO

[설명]

o 폴더가 지정되지 않았으므로 SQL Server 가 설치될 때의 기본 Data 폴더에 만들어집니다.

o 실제 OS상에서 데이터는 자동으로 products2.mdf, products2_log.ldf라는 이름으로 기본 Data 폴더에 저장합니다.

o products2.mdf는 SQL Server 내에서는 products2로 관리되며 products2_log.ldf는 products2_Log라는 이름으로 관리됩니다.

o 데이터의 사이즈는 지정되지 않았으므로 Model 데이터베이스(768KB)의 사이즈와같습니다. 필요한 경우 디스크가 허락하는 한 10%씩 무작정 증가 하게됩니다.

o 로그의 사이즈는 지정되지 않았으므로 자동으로 0.5MB 사이즈로 만들어지고 필요한 경우 하드디스크가 허용하는 한 10% 씩 증가하게 됩니다.

※ SIZE 및 MAXSIZE의 기본 단위는 MB이므로 MB는 생략가능합니다. 하지만 KB를 사용할 경우 KB는 필히 지정해야 합니다. FILEGROWTH는 %를 표시하여 사용 할 수 있습니다. 하지만 기본 단위는 MB이므로 MB는 생락이 가능하지만 %로 지정하고자 하면 필히 %를 입력해야 합니다. FILEGROWTH는 하나의 Extent가 64KB이므로 최소한 64KB이상 지정되어야 합니다.

제 8 장 : 데이터베이스 변경

제가 가장 먼저 접했던 SQL Server는 6.5 버전이었습니다. SQL Server 6.5 버전을 사용하면서 가장 큰 애로사항중의 하나는 수시로 데이터베이스의 사이즈를 확인해서 필요한 경우 사이즈를 늘려주어야 한다는 것과, 데이터베이스 사이즈를 필요에 의해서 줄이고 싶은데 줄일 방법이 없었다는 것입니다(물론 다른 곳에 데이터베이스를 만들어서 Transfer하는 방법이 있기는 했지만).

SQL Server 7.0 버전을 보고 제가 가장 놀랬던 것은 대부분의 관리가 자동화 되었다는 점이었습니다. 데이터베이스 사이즈는 설정만 해주면 필요에 따라 자동으로 늘어나고 필요한 경우 줄일 수도 있다는 사실이 저를 엄청 놀라게 했습니다. 이러한 특징은 SQL Server 2000에서도 마찬가지입니다.

이번 장에서는 데이터베이스 사이즈 늘리기와 줄이기를 중심으로 데이터베이스를 관리하는 방법을 살펴보도록 하겠습니다.

1. 데이터베이스 관리란?

데이터베이스 관리에 포함될 수 있는 작업들에는 다음과 같은 것들이 있습니다.

o 데이터 증가에 따른 파일 사이즈 변경

o 트랜잭션 로그 모니터링

o 필요에 따른 데이터베이스 사이즈 줄이기

o 데이터베이스 삭제

대부분의 작업들은 SQL Server가 자동으로 해주므로 별다른 관여가 필요 없습니다. 하지만 가끔은 수작업이 필요한 경우가 있으므로 그 방법은 알아두는데 좋을듯하여 설명해 봅니다.

2. 데이터베이스 사이즈 늘리기

CREATE 문이 사용되어 생성된 개체에 대해서는 ALTER 문으로 변경이 가능하며 DROP 문으로 삭제가 가능합니다. 그러므로 CREATE 문에 의해 만들어진 데이터베이스는 ALTER 문을 이용하여 변경 할 수 있습니다.(또는 Enterprise Manager를 이용하여 변경할 수 있습니다)

데이터베이스 사이즈를 늘리는 방법은 다음의 세 가지가 있습니다.

o 자동으로 증가하도록 데이터베이스 옵션 설정

o 데이터 파일의 사이즈 확장

o 데이터 파일 추가

1) 자동으로 증가하도록 데이터베이스 옵션 설정

이전의 강좌에서 CREATE DATABASE 문 사용시 사이즈 증가에 대한 SIZE, MAXSIZE, FILEGROWTH 옵션을 기억하시죠? 데이터베이스를 만들 때는 이 옵션들을 이용해서 필요한 경우 사이즈가 자동으로 증가하게 해 주는 것이 좋습니다. 만일 데이터베이스가 꽉 찼는데 사이즈가 늘어나지 않는다면 SQL Server는 더 이상의 데이터를 받아들이지 못하고 경우에 따라서는 아예 작동을 멈추기도 합니다.

데이터베이스의 초기 사이즈는 어느 정도로 할지, MAXSIZE는 어떻게 설정을 할지, 그리고 사이즈 증가가 필요할 때는 어느 정도로 증가가 되게 할지는 관련된 업무를 면밀히 파악해서 결정해야 합니다. 중요한 것은 데이터베이스가 꽉 차는 사태는 없어야 한다는 것입니다. 아무리 사이즈 자동 증가가 되게 했다 하더라도 하드디스크가 여유 공간이 없게되면 더이상의 데이터베이스 사이즈 증가가 불가능하게 되므로 관리자는 하드디스크의 여유 공간도 수시로 확인해 주어야 합니다.

2) 데이터 파일의 사이즈 확장

CREATE DATABASE 문 사용시 'NAME = MyDB_Data' 부분을 기억하시죠? 이 파일의 사이즈를 증가시켜 데이터베이스 사이즈를 증가시켜주는 방법입니다.

[예제1]

ALTER DATABASE MyDB

MODIFY FILE (NAME = MyDB_Data,

SIZE = 30MB)

GO

[설명]

o MODIFY FILE 을 이용하여 Mydb_Data 파일의 사이즈를 30MB로 바꾸었습니다.

3) 데이터 파일 추가

데이터 파일을 추가함으로써 데이터베이스 사이즈를 증가시켜주는 방법입니다.

[예제2]

ALTER DATABASE MyDB

ADD FILE (NAME = MyDB_Data2,

FILENAME = 'D:\Data\MyDB2.ndf',

SIZE = 10MB,

MAXSIZE = 50MB,

FILEGROWTH = 5MB)

GO

[설명]

o ADD FILE 을 이용하여 사이즈가 10MB인 Mydb_Data2 파일을 추가함으로써 데이터베이스 사이즈를 늘렸습니다.

o 이때 FILENAME에 확장자가 ndf 인 파일을 사용했음을 주의하시기 바랍니다.(이해가 안 되시면 이전의 강좌를 참고하시기 바랍니다)

3. 데이터베이스 사이즈 줄이기

이런 경우가 있습니다. 데이터베이스를 사용하다보니 사이즈가 꽤 늘었습니다. 그런데 이중에 필요 없는 테이블을 지웠더니 실제 데이터베이스 사이즈는 500MB인데 데이터가 차지하는 량은 100MB가 되었습니다. 400MB의 빈 공간을 그대로 데이터베이스가 가지고 있게 하는 게 아니고 이 중 300MB 정도는 OS로 반환하여 다른 용도로 사용하게 하고 싶습니다.

이런 경우 데이터베이스 사이즈를 줄여주면 됩니다. 데이터베이스 사이즈를 줄려주는 방법은 데이터베이스 옵션을 설정하여 자동으로 줄어들게 하는 방법과 수동으로 사이즈를 줄이는 방법이 있습니다.

1) 자동 축소기능

데이터베이스 사이즈를 자동으로 줄어들게 하려면 다음과 같이 데이터베이스 옵션에서 '자동 축소[N]'을 선택하여 주시면 됩니다. 그러면 SQL Server는 매 30분마다 체크해서 데이터베이스 사이즈를 줄일 수 있는 경우라면 사이즈를 줄여줍니다.

2) DBCC SHRINKDATABASE

데이터베이스의 사이즈를 지정한 퍼센트만큼의 빈공간이 남은 상태로 줄여줍니다. 하지만 처음 데이터베이스가 만들어질 당시의 사이즈보다 작게 줄일 수는 없습니다. 만일 MyDB 데이터베이스가 처음 10MB의 사이즈였고 지금 100MB인데 많은 부분이 지워져 실제 데이터가 5MB만 차지하고 있다 하더라도 10MB 보다 작게 줄일 수는 없습니다.

[형식]

DBCC SHRINKDATABASE(database_name[,target_percent][,{NOTRUNCATE | TRUNCATEONLY}])

옵션	설명
database_name	줄이고자 하는 데이터베이스 이름
target_percent	전체 사이즈 중에서 빈 공간이 차지하는 사이즈를 얼마로 할것인지 지정
NOTRUNCATE	DBCC SHRINKDATABASE 가 수행되면 데이터베이스 뒤쪽의 데이터를 전부 앞으로 이동 시킨 후 비어진 공간을 제거하는 순서로 진행됩니다. NOTRUNCATE 옵션을 사용하면 데이터를 전부 앞으로 가져오기는 하나 뒤 빈 공간을 제거하지는 않습니다. 결국 데이터베이스 사이즈는 변함이 없습니다. 빈 공간이 OS에 반환되지 않는 데이터 조각모음으로 생각하시면 됩니다.
TRUNCATEONLY	데이터 조각모음은 하지 않고 뒷쪽의 빈 공간만 제거하여 OS에 돌려 줍니다. 만일 데이터베이스에 빈 공간이 앞부분이 100MB가 있고 뒷 부분에 200MB가 있다면 이 옵션에 영향을 받는 것은 뒷 부분의 빈공간 200MB입니다.

[예제1]

현재 MyDB의 데이터베이스 사이즈가 10MB 입니다. 실제 데이터가 6MB밖에 되지 않기 때문에 25%의 빈 공간만 남기고 나머지는 OS에 환원 해주고자 합니다. 방법은 다음과 같습니다.

DBCC SHRINKDATABASE (MyDB, 25)

o 결과적으로 MyDB은 8MB로 줄어들게 됩니다. 8MB에서 6MB의 데이터를 뺀 2MB가 8MB의 25% 이기 때문입니다.(아..어려운 산수!)

[예제2]

DBCC SHRINKDATABASE (MyDB)

o 줄일수 있는 만큼 줄여서 최대한 많은 빈공간을 OS에 반환합니다.

3) DBCC SHRINKFILE

데이터베이스를 이루고 있는 파일의 사이즈를 직접 줄이는 방법으로 처음 데이터베이스가 만들어질 당시의 사이즈보다도 작게 줄일 수 있습니다. 물론 실제 데이터가 차지하고 있는 공간은 줄일 수 없습니다. 만일 MyDB 데이터베이스가 처음 10MB의사이즈였고 지금 100MB인데 많은 부분이 지워져 실제 데이터가 5MB만 차지하고 있다면 5MB까지 줄일 수 있습니다. 단, MODEL 데이터베이스 사이즈 보다 작게는 줄일 수 없습니다.

[형식]

DBCC SHRINKFILE(file_name[,target_percent][,{EMPTYFILE | NOTRUNCATE | TRUNCATEONLY}])

옵션	설명
file_name	줄이고자 하는 데이터 파일의 이름을 지정합니다.
target_percent	데이터 파일을 얼마의 사이즈까지 줄일지 MB단위로 지정합니다.
EMPTYFILE	만일 데이터 파일을 완전히 제거해버리고 싶다면 이 옵션을 사용합니다. 그러면 SQL Server는 해당 데이터 파일에 위치하고 있는 데이터를 동일한 파일그룹내의 다른 데이터 파일로 이동시키고, 더이상 이 데이터 파일에는 데이터가 기록되지 않도록 합니다. 실제 데이터 파일을 제거할 때는 ALTER DATABASE 구문을 사용합니다. 단, PRIMARY 그룹의 데이터 파일에는 적용되지 않습니다.

[예제1]

다음의 예는 MyDB 데이터베이스의 사이즈를 10MB로 줄이는 것입니다.

DBCC SHRINKFILE (MyDB_Data, 10)

o 데이터베이스 이름이 아니고 데이터 파일(MyDB_Data)임을 주의하시기 바랍니다.

4. SQL Server 6.5에서 데이터베이스 사이즈 줄이기

SQL Server 6.5에서는 지금까지 설명된 DBCC SHRINKDATABASE나 DBCC SHRINKFILE 문이 지원되지 않습니다. 그래서 SQL Server 6.5에서의 데이터베이스 사이즈 줄이기가 쉽지 않은데 가장 일반적으로 사용되는 방법은 다른 SQL Server에 원하는 사이즈로 해당 데이터베이스를 새로 만들어 놓고 기존 데이터를 Transfer하는 것입니다. SQL Server 7.0이나 SQL Server 2000에서도 이 방법은 사용 할 수 도 있습니다.

5. 데이터베이스 삭제

신중하게 생각해야 할 부분입니다.

데이터베이스를 삭제하게 되면 물리적인 데이터베이스 파일도 삭제가 됩니다. 만일 데이터베이스를 삭제한 다음에 이를 다시 복구하고자 한다면 백업받은 데이터베이스를 복구하는 방법밖에는 없습니다.

데이터베이스 삭제 할수 있는 방법은 EM(Enterprise Manager)를 사용하는 방법과 DROP DATABASE 문을 사용하는 방법 두가지가 있습니다. 두 방법간의 가장 큰 차이점이라면 DROP DATABASE문은 한번에 여러 데이터베이스를 삭제 할 수 있다는 점입니다.

다음의 경우는 데이터베이스를 삭제 할 수 없습니다.

o RESTORE 중인 데이터베이스

o 사용자가 접속되어 있는 데이터베이스

o REPLICATION에 연관된 데이터베이스(아주 아주 나중에 다루게 됩니다)

o 시스템 데이터베이스

[예제1]

다음의 예는 MyDB와 YourDB 두 데이터베이스를 동시에 삭제하는 것입니다.

DROP DATABASE MyDB, YourDB

제 9 장 : 테이블 생성

1. 테이블 생성(CREATE TABLE)

테이블을 만들기전 테이블에 대한 숫자적인 제약을 살펴보도록 하겠는데, 큰 의미를 둘 필요는 없습니다.

o 하나의 데이터베이스 안에는 20억개의 테이블을 만들 수 있습니다.(어마어마 합니다)

o 각 테이블에는 1,024 개의 컬럼을 만들 수 있습니다.(조금 어마어마 합니다)

o 각 레코드(ROW를 가리킵니다)는 이미지나 텍스트가 이닌 경우 8,060 Byte의 최대 용량을 갖습니다.

※ 이미지와 텍스트는 데이터 페이지가 아닌 다른 공간에 저장이 됩니다. 이들이 저장된 공간의 위치만을 데이터 페이지에 기록하게 됩니다(16Byte 차지). 이미지와 텍스트가 가질 수 있는 최대 용량은 2GB 입니다. 만일 sp_tableoption 시스템 스토어드프로시저를 이용하면 이미지와 텍스트를 데이터 페이지에 저장 할 수 있습니다.

테이블을 생성하는 방법역시 EM(Enterprise Manager)를 사용하는 방법과 SQL문을 사용하는 방법 두 가지가 있습니다.

1. EM을 이용한 테이블 생성

다음 [그림 1]과 같이 테이블 항목에서 마우스 오른쪽버튼을 눌러 [새 테이블(B)...]을 선택하시면 됩니다.

[그림 1]

다음의 [그림 2]는 EM에서 테이블을 만드는 과정의 화면입니다.

[그림 2]

2. CREATE TABLE문을 이용한 테이블 생성

EM에서 작성 할 때 사용되는 화면과 아이템들이 CREATE TABLE 문의 어느 부분에 해당되는지 생각을 해보시기 바랍니다.

[예제1]

다음의 예는 MyDB에 MyTable이라는 테이블을 만드는 것입니다.

USE MyDB

GO

CREATE TABLE MyTable (

UserID char(05) NOT NULL,

UserName varchar(20) NOT NULL,

Address varchar(100) NULL

)

o 테이블을 만들 때는 테이블의 이름과 컬럼이름, 컬럼의 데이터 타입 그리고 각 칼럼들이 NULL값을 가질지의 여부를 지정하게 됩니다.

o 데이터 타입에 대한 정확한 정보는 Books Online을 참고하여 주시기 바랍니다.

3. NULL or NOT NULL

null 값은 값이 없다거나 값이 0임을 가리키는 것이 아니고 값이 무엇인지 모르겠다는 의미입니다. 그리므로 null 값을 가지고 다른 값과 비교 할 수 없으며 심지어 null 값 서로 간에도 비교를 할 수 없습니다. 컬럼을 선언할 때 NOT NULL 이라고 하는 것은 이 컬럼에는 NULL 이 아닌 분명한 값이 들어와야 한다는 것을 지정하는 것입니다. NULL 이라고 선언하게 되면 특정 값이 들어오지 않으면 NULL로 지정하겠다는 것입니다.

다음은 Books Online에서 설명하고 있는 null에 관한 설명입니다.

o 쿼리에서 null 값을 검사하려면 WHERE 절에서 IS NULL이나 IS NOT NULL을 사용합니다.

o SQL 쿼리 분석기에서 쿼리 결과가 나타나면 결과 집합에서 null 값이 NULL로 표시됩니다.

o null 값은 INSERT 또는 UPDATE 문에서 NULL을 명시적으로 입력하거나, INSERT 문에서 해당 열을 제외시키거나, ALTER TABLE 문을 사용하여 기존 테이블에 새 열을 추가할 때 열에 입력됩니다.

o 테이블의 행을 다른 행과 구별하는 데 필요한 정보(예: 외래 키나 기본 키)에는 null 값을 사용할 수 없습니다.

4. 테이블 생성의 여러가지 예

CREATE TABLE에 대한 몇가지 예를 살펴봄으로써 추가적인 내용을 살펴보도록 하겠습니다.

[예제1]수식의 사용

CREATE TABLE MyTable

(

low int,

high int,

myavg AS (low + high)/2

)

o low와 high에 값이 입력되면 myavg에는 두값의 합을 2로 나눈 값이 저장됩니다.

o 다음의 쿼리 수행 결과에서 myavg는 15라는 값을 같게 됩니다.

INSERT INTO MyTable(low, high) VALUES(10, 20)

GO

SELECT * FROM MyTable

[예제2] 함수의 사용

CREATE TABLE MyLoginTable

(

date_in datetime,

user_id int,

myuser_name AS USER_NAME()

)

o USER_NAME 함수를 사용해서 새로운 레코드가 생길 때 myuser_name에는 세션을 연결한 사용자 정보가 입력되도록 한 예입니다.

o date_in 컬럼에 자동으로 현재의 날짜가 입력되게 하려면 위 쿼리문을 어떻게 고치면 될까요? 다음과 같이 GETDATE 함수를 이용하시면 됩니다.

CREATE TABLE MyLoginTable

(

date_in AS GETDATE(),

user_id int,

myuser_name AS USER_NAME()

)

[예제3] IDENTITY 사용

CREATE TABLE TestIdentity

(

user_id int IDENTITY (1,1),

user_name char(20)

)

GO

INSERT INTO TestIdentity(user_name) values('AAA')

INSERT INTO TestIdentity(user_name) values('BBB')

SELECT * FROM TestIdentity

o 새로운 레코드가 생기면 user_id에는 1부터 시작해서 1씩 증가한 값이 입력됩니다. SELECT 결과에서 'AAA'는 user_id가 1, 'BBB'는 user_id 가 2 로 기록됩니다.

[예제4] 임시 테이블

USE MyDB

GO

CREATE TABLE #MyTempTable

(

user_id int,

user_name char(20)

)

o #을 붙여 만든 테이블은 연결된 세션이 종료되면 자동으로 사라지며, 세션이 연결된 상태라 하더라도 다른 세션에서는 사용 할 수 없습니다.

o 만일 세션이 연결된 동안 다른 세션에서도 이 임시 테이블을 사용하게 하려면 # 대신 ##을 붙여 만들면 됩니다. #가 붙으면 로컬 임시테이블, ##가 붙으면 글로벌 임시테이블 이라고 합니다.

제 10 장 : 데이터 무결성

앞의 강좌의 마지막 부분의 퀴즈의 정답은 Tempdb 입니다. 임시테이블은 Tempdb에 만들어졌다가 사라지게 됩니다. 만일 실제 테이블을 Tempdb에 만들어 놓게 되면 일반 테이블과 다를 바가 없지만 SQL Server가 Restart 되면 이 테이블들은 사라지게 됩니다.

테이블에 저장되는 데이터의 무결성을 위해서 몇 가지 제약(constraints)를 걸 수 있습니다. 예를 들면 전화번호 컬럼에는 숫자만 허용한다든지, 주민번호 컬럼에는 주민번호가 유일해야 한다든지 등입니다. 이러한 제약을 통해서 잘못된 데이터가 테이블에 들어오게 되는 것을 미리 방지할 수 있게 됩니다. 이러한 제약(constraints)에 대하여 살펴보도록 하겠습니다.

1. DEFAULT

INSERT 시에 별도의 값이 지정되지 않으면 특정 값이 기본값으로 해당 컬럼에 채워지게 하는 것입니다. 당연히 하나의 컬럼에는 오직 하나만의 DEFAULT 제약이 가능합니다.

예를 들어 Friend 테이블의 Address 컬럼에 별도의 값이 지정되지 않으면 '알수없음' 이라는 문자열로 대신 채우고 싶으면 다음과 같이 DEFAULT 제약을 주면 됩니다.

CREATE TABLE Friend (

Name varchar(10),

Address varchar(50)

)

GO

ALTER TABLE Friend

ADD CONSTRAINT DFT_Address DEFAULT '알수없음'

FOR Address

GO

INSERT INTO Friend VALUES('이장래','서울시 거기구 거기동')

INSERT INTO Friend(Name) VALUES('한국인')

위 예에서 처음의 INSERT문에서는 주소가 지정되었지만 두 번째 INSERT문에는 주소가 생략되었습니다. 이 경우 Address 컬럼에는 '알수없음' 이라는 문자열이 주소를 대신하게 됩니다.

2. CHECK

특정 컬럼에 값이 입력될 때 특정 조건을 만족하는 경우만 받아들이게 할 경우 사용합니다. 예를 들어 Member 테이블의 Age 컬럼에는 20 에서30 까지의 수만 입력이 되게 하고 싶다면 다음과 같이 CHECK 제약을 주면됩니다.

CREATE TABLE Member (

Name varchar(10),

Age int

)

GO

ALTER TABLE Member

ADD CONSTRAINT CHK_Age CHECK (Age BETWEEN 20 AND 30)

GO

INSERT INTO Member VALUES('이장래',20)

INSERT INTO Member VALUES('한국인',50)

마지막 INSERT문에서는 다음과 같은 오류가 발생합니다.

서버: 메시지 547, 수준 16, 상태 1, 줄 1

INSERT 문이 COLUMN CHECK 제약 조건 'CHK_Age'과(와) 충돌되었습니다. 충돌은 'Sample' 데이터베이스, 'Member', column 'Age' 테이블에서 발생했습니다.

문이 종료되었습니다.

3. PRIMARY KEY

너무나 중요하죠?

테이블에 Primary Key를 선언해서 중복되는 row 가 없도록 하는 것입니다. PRIMARY KEY 제약은 다음의 특징이 있습니다.

o 유일한 값이 들어와야 합니다.

o null값이 들어올 수 없습니다.

o 한 테이블에 오직 한 개의 PRIMARY KEY 제약을 걸 수 있습니다.

o 자동으로 UNIQUE 인덱스가 만들어집니다. CLUSTERED 또는 NONCLUSTERED 인덱스중 한 가지를 선택 할 수 있습니다. 별도로 지정하지 않으면 CLUSTERED 인덱스가 됩니다.

CREATE TABLE Member2 (

Member_ID char(05) NOT NULL,

Member_Name varchar(10),

Address varchar(50)

)

GO

ALTER TABLE Member2

ADD CONSTRAINT PK_Member PRIMARY KEY NONCLUSTERED (Member_ID)

GO

INSERT INTO Member2 VALUES('1111','이장래','서울')

INSERT INTO Member2 VALUES('1111','홍길동','부산')

Member_ID 컬럼 선언시 NOT NULL로 지정한 것이 보입니다. 만일 NOT NULL 이 지정되지 않으면 Member_ID에 대하여 PRIMARY KEY 제약을 걸 수 없습니다.

위의 두 INSERT문에서 마지막 INSERT문은 다음과 같은 오류를 발생시킵니다.

서버: 메시지 2627, 수준 14, 상태 1, 줄 1

PRIMARY KEY 제약 조건 'PK_Member'을(를) 위반했습니다. 'Member2' 개체에 중복키를 삽입할 수 없습니다.

문이 종료되었습니다.

데이터 무결성 차원에서 PRIMARY KEY 제약은 그 어떤 제약보다 중요하다고 생각합니다. 테이블을 디자인 할 때 Primary Key가 없으면 무언가 잘못되었다고 생각 할 수 있습니다. 예전에 실무에서 사원 테이블에 Primary Key가 선언되어 있지 않아 동일한 사번이 두개 있어 문제가 발생한 적이 있습니다.

만일 Application을 개발한다면 테이블에 여러가지 제약을 주는 것도 중요하지만 위와 같은 오류가 발생 했을 때 Application 사용자에게 메세지를 정확하게 출력해 주는 부분을 개발해야 합니다. 그렇지 않으면 Runtime 오류가 발생해서 Application 사용자를 짜증나게 합니다.

4. UNIQUE

컬럼의 값이 중복되지 않게 합니다. 같은 주민등록 번호가 들어오지 못하게 한다거나, 사원번호가 중복되어 들어오지 못하게 한다거나 할 때 사용합니다. 얼핏 보면 PRIMARY KEY 제약과 같아 보이나 null 도 하나의 값으로 인식된다는 것과 한 테이블에 여러개(즉 주민번호도 중복되지 않으면서 사원번호도 중복되지 않게) 사용할 수 있다는 점에서 다릅니다.

CREATE TABLE Member3 (

Member_ID char(05),

Member_RSDN char(13),

Address varchar(50)

)

GO

ALTER TABLE Member3

ADD CONSTRAINT UQ_Member3_ID UNIQUE NONCLUSTERED (Member_ID)

GO

ALTER TABLE Member3

ADD CONSTRAINT UQ_Member3_RSDN UNIQUE NONCLUSTERED (Member_RSDN)

GO

INSERT INTO Member3 VALUES('1111','7007071234567','서울')

INSERT INTO Member3 VALUES('2222','7007071234567','부산')

마지막 INSERT문에서는 다음과 같은 오류가 발생합니다.

서버: 메시지 2627, 수준 14, 상태 2, 줄 1

UNIQUE KEY 제약 조건 'UQ_Member3_RSDN'을(를) 위반했습니다. 'Member3' 개체에 중복 키를 삽입할 수 없습니다.

문이 종료되었습니다

5. FOREIGN KEY

만일 부서 테이블이 있고 사원 테이블이 있다고 가정을 하겠습니다. 부서 테이블에는 부서 코드와 그 부서에 대한 몇 가지 정보가 있을 것입니다. 그리고 사원 테이블에는 사원번호와 사원에 대한 몇 가지 정보가 있을 터인데 그중에 어느 부서에 근무하는지 표시하기 위해 부서 코드가 존재할 것입니다. 이때 우리는 사원 테이블에 기록되는 부서 코드는 분명히 부서 테이블에 등록된 부서 코드일거라 확신을 하게 됩니다. 이러한 확신을 도와주기 위해서 FOREIGN KEY 제약을 겁니다. 즉, 사원테이블의 부서코드는 부서 테이블의 부서코드를 참조(Reference)하는 Foreign Key라고 선언하는 것입니다.

다음의 예를 보면서 그 기능을 확인해 보도록 하겠습니다.

CREATE TABLE Department (

Dept_Code char(05) NOT NULL,

Dept_Name char(10),

Dept_Manager char(05)

)

GO

ALTER TABLE Department -- 1)

ADD CONSTRAINT PK_Department PRIMARY KEY NONCLUSTERED (Dept_Code)

GO

CREATE TABLE Employee (

Emp_Code char(05) NOT NULL,

Emp_Name char(10),

Dept_Code char(05),

Address varchar(50)

)

GO

ALTER TABLE Employee -- 2)

ADD CONSTRAINT FK_Employee FOREIGN KEY(Dept_Code)

REFERENCES Department(Dept_Code)

GO

INSERT INTO Department VALUES('10000','영업부','A0001')

INSERT INTO Department VALUES('20000','인사부','A0010')

INSERT INTO Employee VALUES('A0015','이장래','10000','서울')

INSERT INTO Employee VALUES('A0023','한국인','30000','부산') -- 3)

o 1) 에 의해서 Department 테이블에서 Dept_Code는 Primary Key입니다.

o 2) 에 의해서 Employee 테이블에서 Dept_Code는 Department 테이블의 Dept_Code를 참조하는 Foreign Key 입니다.

o 마지막 INSERT문 3) 에서는 다음과 같은 오류가 발생합니다. 왜냐하면 INSERT되는 '30000' 값은 Department 테이블에 존재하지 않기 때문입니다.

서버: 메시지 547, 수준 16, 상태 1, 줄 1

INSERT 문이 COLUMN FOREIGN KEY 제약 조건 'FK_Employee'과(와) 충돌되었습니다. 충돌은 'Sample' 데이터베이스, 'Department', column 'Dept_Code' 테이블에서 발생했습니다.

문이 종료되었습니다.

만일 이 상태에서 다음의 쿼리문을 수행하면 어떻게 될까요?

DELETE FROM Department WHERE Dept_Code = '10000'

위 DELETE문은 수행되지 않고 오류가 발생합니다. 그 이유는 Dept_Code '10000'은 Employee 테이블에 처음 INSERT 된 '이장래' 가 참조하고 있는 값이기 때문입니다. 그래서 다음과 같은 오류가 발생합니다.

서버: 메시지 547, 수준 16, 상태 1, 줄 1

DELETE 문이 COLUMN REFERENCE 제약 조건 'FK_Employee'과(와) 충돌되었습니다. 충돌은 'Sample' 데이터베이스, 'Employee', column 'Dept_Code' 테이블에서 발생했습니다.

문이 종료되었습니다.

위 DELETE문이 수행되기 위해서는 우선 Dept_Code 가 '10000'인 레코드를 Employee 테이블에서 DELETE해야 합니다.

위와 같이 FOREIGN KEY 제약은 데이터의 무결성을 유지시켜줍니다.

다음 퀴리문은 어떠한 의미를 가지고 있는지 분석해 보시기 바랍니다.

CREATE TABLE Employee2 (

emp_code char(05) NOT NULL CONSTRAINT emp_code UNIQUE,

mgr_code char(05) NOT NULL CONSTRAINT mgr_code

REFERENCES Employee2(emp_code))

제 11 장 : 인덱스(Index)

1. 인덱스(INDEX)를 몰랐던 나

처음 SQL Server를 접한 것은 버전 6.5였습니다. 업체에서 설치해 주고 간 SQL Server에 내가 아는 오직 한 가지 CREATE TABLE을 이용해서 테이블을 만들었습니다. 그리고 이런 방법 저런 방법을 이용해서 IBM AS/400에서 데이터를 이 테이블로 내려 부었습니다. 다운사이징이 되었다는 기쁨에 Visual Basic을 이용해서 데이터를 조회하는 Application을 작성했습니다.

"으헥~이게 뭐야. 차라리 Access가 낫겠구먼~"

너무나 느렸습니다. 조회 버튼 누르고 수 분이 걸려야 결과가 나왔습니다. 하지만 관련 서적을 뒤적이다 인덱스의 존재를 알고 이를 반영하고 나니 모든게 달라졌습니다.

"으와~진짜 빠르다!"

2. 인덱스를 만드는 이유

인덱스를 만드는 이유는 가장 큰 이유가 데이터를 빠르게 검색하기 위함 입니다. 베개만한 두께의 SQL Server 참고 서적이 있다고 할 때 이 책에서 CONSTRAINT에 대한 내용을 찾고자 한다면 우리는 책 전체를 뒤지는 게 아니고 책 뒤의 인덱스 부분을 참조하여 CONSTRAINT가 위치한 페이지를 확인하고 그 페이지로 이동하여 원하는 내용을 참조하게 됩니다. 데이터 검색도 마찬가지 입니다. 인덱스가 없다면 테이블 전체를 읽어 원하는 데이터를 찾아야 합니다.

인덱스를 만드는 두 번째 이유는 Row의 유일성을 유지하기 위한 것입니다. UNIQUE 인덱스가 여기에 해당합니다. 예를 들어 사원 테이블에는 동일한 사원번호를 갖는 사원 정보가 있어서는 안 됩니다. 이런 경우 사원번호에 인덱스를 이용하여 유일성을 부여할 수 있습니다.

3. 인덱스의 안 좋은 점

인덱스가 좋다고 무작정 좋은 것은 아닙니다. 인덱스를 만들게 되면 그 정보를 유지하기 위해서 디스크 공간도 필요하게 되고, 인덱스가 걸려 있는 테이블은 인덱스가 없을 때보다 데이터를 추가하거나 변경할 때 너 많은 시간이 소요 됩니다. 이런 이유로 인덱스를 만들 때 해당 테이블의 용도를 정확히 이해해야 합니다. 만일 읽기 전용의 테이블이라면 인덱스를 걸어 주는 게 좋고, 수정 및 추가가 엄청나게 이루어지는 테이블이라면 인덱스 거는 것이 역효과가 날 수 있습니다. 처음 인덱스를 알게 된 저는 이런 사실을 모르고 무작정 인덱스를 만들었습니다. 결국엔 모든 컬럼에 인덱스가 걸려 배보다 배꼽이 더 커져버린 결과가 되었습니다.

4. 인덱스의 종류

인덱스의 종류를 굳이 나누자면 다음의 두 가지로 분류 할 수 있습니다.

o Clustered Index

o Nonclustered Index

Clustered Index 는 영어 사전을 생각하시면 쉽게 이해 할 수 있습니다. 영어 사전은 자체가 알파벳 순서로 정렬되어 있기 때문에 우리가 찾고자 하는 단어를 쉽게 찾을 수 있습니다.

Nonclustered Index는 책 뒤에 있는 인덱스를 생각하지면 됩니다. 책 여기저기에 흩어진 내용을 이 인덱스를 이용하면 그 위치(페이지)를 쉽게 찾을 수 있습니다.

인덱스는 SQL Server Performance 튜닝에 있어서 가장 기본적인 것입니다. 물론 비싼 돈을 드려 빵빵한 서버를 구축하는 것도 성능 향상을 꾀하는 방법이긴 하지만 돈 안들이고 성능을 향상 시키는 방법, 그것이 인덱스 입니다. 앞으로 꽤 많은 강좌가 인덱스를 설명하게 위해 할애될 것 같습니다. 자 시작해 볼까요?

5. 연습용 테이블 만들기

우선 다음과 같은 구조의 Customers 테이블을 만들었다고 가정을 하겠습니다.

CREATE TABLE Customers (

cust_id char(05) NOT NULL ,

cust_name varchar (40) NOT NULL ,

phone char (12) NOT NULL ,

address varchar (40) NULL

)

6. PRIMARY KEY를 지정할 때 생성되는 인덱스

앞에서 만들어진 Customers 테이블에 cust_id 컬럼을 Primary Key로 지정하고자 다음과 같이 수행하였습니다.

ALTER TABLE Customers

ADD CONSTRAINT PK_Customers PRIMARY KEY (cust_id)

GO

이와 같이 Primary Key를 만들게 되면 자동적으로 PK_Customers라는 이름의 인덱스가 생성이 됩니다. 인덱스의 종류를 지정하지 않은 경우 Primary Key 생성시에는 기본적으로 Clustered Index 가 됩니다. 또한 Primary Key는 유일성을 보장하는 것이므로 Unique Index가 됩니다. 만일 Primary Key를 만들 때 Nonclustered Index가 되게 하고자 한다면 다음과 같이 NONCLUSTERED라고 지정해야 합니다.

ALTER TABLE Customers

ADD CONSTRAINT PK_Customers PRIMARY KEY NONCLUSTERED (cust_id)

GO

생성된 인덱스의 존재를 확인하고자 한다면 다음과 같이 sp_helpindex 를 이용하면 됩니다.

sp_helpindex Customers

7. PRIMARY KEY가 꼭 Clustered Index가 될 필요는 없습니다.

앞에서 확인했다시피 별도의 지정이 없으면 Primary Key는 Clustered Index가 됩니다. 이런 이유로 많은 분들이 Primary Key는 항상 Clustered Index라고 생각하시는 경우가 있는데 그렇지 않습니다. 필요한 경우 Primary Key를 Nonclustered Index 로 하고 별도의 Clustered Index를 만들 수 있습니다.

다음의 예는 Customers 테이블에서 cust_name에 Clustered Index를 생성하는 것입니다.

CREATE CLUSTERED INDEX CL_cust_name ON Customers (last_name)

o 인덱스를 만들 때는 CREATE INDEX 문을 이용합니다.

o CL_cust_name 이 인덱스 이름이 됩니다.

o Customers (last_name) 은 Customers 테이블의 last_name 컬럼을 지정하는 것입니다.

주의 할 것은 Clustered Index는 한 테이블에 오직 한개만 존재 할 수 있다는 사실입니다.

8. 인덱스 만들기(CREATE INDEX)

앞의 강좌에서 마지막 부분에 잠깐 언급을 했듯이 인덱스를 만들 때는 CREATE INDEX 문을 사용합니다. 물론 Enterprise Manager를 이용해서 인덱스를 만드는 방법도 있지만 여기서 다루지는 않겠습니다.

다음은 인덱스에 대한 숫자적인 제약입니다.

o Clustered Index는 한테이블에 오직 한개만 가능

o Nonclustered Index는 한 테이블에 249개 까지 가능

o 한 인덱스에 포함 될 수 있는 컬럼은 최대 16개까지

보통 현업에서 한 테이블에 인덱스가 5개 정도 됩니다. 그러므로 249개까지 가능하다는 것은 실무에는 별 의미가 없다고 생각합니다. 컬럼이 16개나 인덱스에 포함될 경우도 거의 없습니다. 인덱스를 만들 때 어떤 인덱스를 만들지 결정을 해야 합니다. 즉 Clustered Index로 할지 Nonclustered Index로 할 지, Unique 를 지정할지 말지를 결정해야 합니다. 이를 결정하기 위해서는 해당 테이블이 어떠한 용도로 사용되는지 업무를 정확히 파악을 하고 있어야 합니다. 인덱스를 만드는 방법은 너무나 간단합니다. 하지만 인덱스를 어떻게 만들 것인지 결정하는 것은 그리 쉬운 일이 아닙니다. 많은 고민이 필요합니다.

예제를 보면서 인덱스를 만드는 방법을 확인해보도록 하겠습니다.

[예제1]

CREATE INDEX idx1 ON Customers(cust_id)

o 어떤 종류의 인덱스를 만들지 설정되지 않았습니다.

o 이런 경우 기본적으로 Unique 하지 않은 Nonclustered Index가 됩니다.

[예제2]

CREATE CLUSTERED INDEX idx1 ON Customers(cust_id)

o 인덱스 종류는 Clustered로 지정 되었으므로 Clustered Index가 만들어집니다.

o 하지만 Unique는 지정되지 않았습니다. 이런 경우 [예제 1] 처럼 Unique 하지 않은 Index가 됩니다.

[예제3]

CREATE UNIQUE INDEX idx1 ON Customers(cust_id)

o Unique 한 Nonclustered Index 가 만들어집니다.

[예제4]

CREATE UNIQUE CLUSTERED INDEX idx1 ON Customers(cust_id)

o Unique 한 Clustered Index 가 만들어집니다.

[예제5]

CREATE CLUSTERED UNIQUE INDEX idx1 ON Customers(cust_id)

o 오류가 발생합니다.

o UNIQUE는 CLUSTERED 나 NONCLUSTERED 앞에 위치해야 합니다.

9. Nonclustered Index 구조

꼭 인덱스 구조를 알고 있을 필요는 없지만 알아두면 피가 되고 살이 되기 때문에 간단하게 언급을 하고자 합니다. 아래 이미지는 Books Online에 있는 이미지입니다. 이 이미지를 보면서 몇 가지 사항을 확인해보도록 하겠습니다.

인덱스는 위 이미지와 같이 B-트리 구조로 되어 있습니다. 마치 나무를 거꾸로 세워놓은 듯한 구조이기 때문에 위처럼 뿌리(root)가 보이고 잎이 보입니다.

1) sysindexes 시스템 테이블

sysindexes 테이블에는 인덱스에 대한 정보가 기록되어 있습니다. 인덱스 정보를 기록하고 있는 실제 인덱스 페이지가 어디에 있는지 인덱스의 종류가 무엇인지 등이 포함되어 있습니다. 나중에 기회가되면 자세히 확인해보도록 하겠습니다.

2) 용어설명

o 루트노드(Root node) : 인덱스는 위 이미지처럼 나무를 꺼꾸로 세워놓은듯(그렇게 안보이나요?)한 모양을 하고 있는 트리구조(B-트리구조라고 부릅니다)로 되어 있는데 이 트리의 시작을 Root node라고 합니다. 모든 인덱스 검색은 이 Root nodel에서 시작됩니다.

o 잎노드: 보통 Leaf Level이라고 부릅니다. 위의 이미지는 너무 단순해서 모양이 나타나지 않지만 하나의 뿌리에서 자라나온 가지에 마지막으로 달린 잎같다고 해서 Leaf Level이라고 부릅니다. Nonclustered Index에서는 이 Leaf Level에 실제 데이터가 존재하는 포인터가 기록되어 있습니다.(Clustered Index에서는 이 Leaf Level이 Data Level이 됩니다)

o 중간노드 : 보통 Non-Leaf Level 이라고 부릅니다. 위 이미지에서는 표시되지 않았지만 Root node와 Leaf Level사이에 더 많은 인덱스 페이지가 있을 수 있습니다. 이를 Non-Leaf Level 이라고 부릅니다.

o 데이터 페이지 : 실제 데이터가 저장되는 페이지를 Data Page라고 합니다.

10. UNIQUE 인덱스 만들기

인덱스를 만드는 이유가 대부분은 검색 속도를 빠르게 하기 위함이라고 생각하고 있습니다. 이게 틀린 말은 아니지만 인덱스를 만드는데는 유일성을 유지하여 데이터 무결성을 구현한다는 중요한 목적도 포함되어 있습니다. 물론 UNIQUE 제약을 이용해서 이를 구현 할 수 있지만 CREATE INDEX 문을 이용해서도 가능합니다. 사실 PRIMARY KEY나 UNIQUE 제약을 설정하면 내부적으로 자동적으로 UNIQUE 인덱스가 만들어 집니다.

UNIQUE인덱스에 대한 몇 가지 특징을 살펴보겠습니다.

o PRIMARY KEY나 UNIQUE 제약을 설정하면 자동적으로 UNIQUE 인덱스가 만들어집니다.

o 만일 테이블에 데이터가 존재하는 상태라면 UNIQUE 인덱스를 만들 때 중복되는 값이 있는지 체크 합니다. 만일 중복된 값이 있다면 UNIQUE 인덱스를 만들 수 없습니다.

o UNIQUE 인덱스가 설정되어 있으면 INSERT나 UPDATE시 데이터 값의 중복 여부를 확인해서 중복되면 값을 받아들이지 않고 에러메세지를 표시합니다.

[예제1]

CREATE TABLE A (

col1 CHAR(2),

col2 INT)

GO

INSERT INTO A VALUES ('AA',123)

INSERT INTO A VALUES ('AA',345)

GO

CREATE UNIQUE INDEX IDX1 ON A(col1)

GO

o 두 번의 INSERT에 의해서 col1에는 'AA'라는 중복된 값이 입력된 상태에서 UNIQUE 인덱스를 생성하려고 했습니다.

o 이 경우 다음과 같은 오류가 발생하면서 UNIQUE 인덱스를 만들지 못합니다.

서버: 메시지 1505, 수준 16, 상태 1, 줄 1

인덱스 ID 2에 중복 키가 있어 CREATE UNIQUE INDEX가 종료되었습니다. 가장 중요한 기본 키는 'AA'입니다.

문이 종료되었습니다.

[예제2]

CREATE TABLE B (

col1 CHAR(2),

col2 INT)

GO

CREATE UNIQUE INDEX IDX1 ON B(col1)

GO

INSERT INTO B VALUES ('AA',123)

INSERT INTO B VALUES ('AA',345)

GO

o col1 컬럼에 대하여 UNIQUE 인덱스를 만든 상황에서 중복되는 값 'AA'가 INSERT되려고 합니다.

o 두번째 INSERT문은 다음과 같은 오류가 발생하면서 INSERT가 거부됩니다.

서버: 메시지 2601, 수준 14, 상태 3, 줄 1

고유 인덱스 'IDX1'이(가) 있는 'B' 개체에 중복 키 행을 삽입할 수 없습니다.

문이 종료되었습니다.

o Unique 한 Clustered Index 가 만들어집니다.

[예제3]

CREATE TABLE AA (

col1 CHAR(2),

col2 INT)

GO

INSERT INTO AA VALUES ('AA',123)

INSERT INTO AA VALUES ('AA',345)

CREATE TABLE BB (

col1 CHAR(2),

col2 INT)

GO

CREATE UNIQUE INDEX IDX1 ON BB(col1)

GO

INSERT INTO BB SELECT * FROM AA

마지막 INSERT문에 의해서 테이블 BB에는 몇개의 레코드가 INSERT 될까요?

[예제3]

CREATE TABLE AA (

col1 CHAR(2),

col2 INT)

GO

INSERT INTO AA VALUES ('AA',123)

INSERT INTO AA VALUES ('AA',345)

CREATE TABLE BB (

col1 CHAR(2),

col2 INT)

GO

CREATE UNIQUE INDEX IDX1 ON BB(col1)

GO

INSERT INTO BB SELECT * FROM AA

o 마지막 INSERT문에 의해서 테이블 BB에는 아무 레코드도 INSERT 되지 못합니다.

o 처음 한 레코드가 INSERT되고 두번째 레코드가 INSERT 되려고 할 때 중복 값이 확인되어 모든 처리가 취소되어 버립니다.

[예제4]

CREATE TABLE AA (

col1 CHAR(2),

col2 INT)

GO

INSERT INTO AA VALUES ('AA',123)

INSERT INTO AA VALUES ('AA',345)

CREATE TABLE BB (

col1 CHAR(2),

col2 INT)

GO

CREATE UNIQUE INDEX IDX1 ON BB(col1)

WITH IGNORE_DUP_KEY

GO

INSERT INTO BB SELECT * FROM AA

o CREATE UNIQUE 문에 WITH IGNORE_DUP_KEY 옵션이 포함되어 있습니다. 이는 레코드가 입력이 될 때 중복된 값은 받아 들이지 않더라고 나머지는 받아들이라는 설정입니다.

o 처음 한 레코드가 INSERT되고 두번째 레코드가 INSERT 되려고 할 때 중복 값이 확인되어 두번째 INSERT는 무시되고 처음의 INSERT는 반영이 됩니다. 하지만 다음과 같은 메세지는 표시됩니다.

서버: 메시지 3604, 수준 16, 상태 1, 줄 1

중복 키가 무시되었습니다.

[예제5]

SELECT col1, COUNT(col1)

FROM AA

GROUP BY col1

HAVING COUNT(col1) > 1

ORDER BY col1

o UNIQUE 인덱스를 만들기 전에 중복되는 값이 있나 확인하는 방법입니다.

o 간단한 쿼리문이지만 참으로 유용하게 사용됩니다. 이해하기 어려운 것이 아니므로 자세한 설명은 생략합니다.

11. COMPOSITE 인덱스

하나의 컬럼에 대하여 인덱스를 만들라는 제약은 없습니다. 필요한 경우 여러 개의 컬럼을 대상으로 인덱스를 만들 수 있습니다. 이와 같은 인덱스를 COMPOSITE 인덱스라고 합니다. COMPOSITE 인덱스에는 최대 16개까지의 컬럼을 포함 시킬 수 있지만 실무에서는 많아야 3~4개 정도의 컬럼이 사용됩니다. COMPOSITE 인덱스를 만들어야 하는 경우는 다음의 두 가지로

우선 다음의 예를 보도록 하겠습니다.

[예제1]

CREATE TABLE Customer (

cust_id CHAR(5),

cust_name CHAR(10),

city CHAR(6),

grade CHAR(1))

GO

SELECT * FROM Customer

WHERE city = '서울' AND grade = 'A'

GO

o SELECT문에서 WHERE부분에 city와 grade 두 컬럼을 비교하고 있습니다.

o 이러한 검색이 성능이 좋게 하고자 한다면 다음과 같이 COMPOSITE 인덱스를 만들어주면 됩니다.

CREATE INDEX idx1 ON Customer (city, grade)

GO

o 앞에서 배운 것 복습해 볼까요? UNIQUE가 설정되지 않았고 CLUSTERED 여부도 설정되지 않았습니다. 그렇다면 UNIQUE 하지 않은 NONCLUSTERED 인덱스가 만들어집니다. 기억하시죠?

o 컬럼을 지정하는 부분에 city, grade 두 컬럼이 나열되어 있습니다. 이것이 COMPOSITE 인덱스를 만드는 방법입니다.

여기서 한 가지 의문이 생깁니다. 인덱스를 구성하는 컬럼의 순서가 city, grade인 것과 grade, city 인것 두 가지가 동일한 성능을 제공할까 하는 것과, 만일 다르다면 어떤 순서가 더 나은 성능을 제공할까 하는 의문입니다.

o 인덱스를 구성하는 컬럼의 순서가 다르다면 그 효과역시 다릅니다.

o 인덱스를 구성하는 컬럼의 순서를 결정하는 것은 테이블의 데이터에 달려 있습니다.

o 만일 위 [예제 1]의 Customer 테이블에서 city 컬럼과 grade 컬럼중 어느 것이 선택성(Selectivity)가 좋은지에 따라 컬럼의 순서를 정해야 합니다. 예를 들어 전체 레코드가 10,000개이고, 이중에서 city = '서울'의 조건을 만족하는 레코드가 1,000개이고 grade = 'A'를 만족하는 레코드가 100개라면 인덱스를 구성하는 컬럼의 순서는 grade, city 가 되어야 한다는 것입니다. 즉 다음과 같이 인덱스를 만들어야 합니다.

CREATE INDEX idx1 ON Customer (grade, city)

GO

o WHERE 절도 마찬가지 입니다. 선택성이 좋은(다시 말하면 UNIQUE 한 값이 많은) 컬럼을 앞으로 가져와야 더 나은 성능을 제공 받을 수 있습니다.

※ 선택성(Selectivity)

여기 1부터 1,000까지의 숫자가 있습니다.

1, 2, 3, 4, 5 . . . . . .555, 556, 557. . . . . . 997, 998, 999, 1000

① 이 중에서 2의 배수는 500개 입니다.

② 이 중에서 5의 배수는 200개 입니다.

③ 이 중에서 10의 배수는 100개 입니다.

④ 이 중에서 100의 배수는 10개 입니다.

⑤ 이 중에서 1000의 배수는 1개입니다.

위 결과로 볼 때 선택성이 가장 좋은 것은 ⑤번 입니다. 가장 선택성이 않좋은 것은 ① 번 입니다. 즉, 검색한 결과가 전체 데이터에서 차지하는 비율이 낮을 때 선택성은 좋다고 이야기 합니다. 이러한 컬럼들이 인덱스를 구성하는 컬럼의 후보가 됩니다.

12. FILLFACTOR & PAD_INDEX

테이블에 데이터가 INSERT 되거나 UPDATE되면(즉 데이터 페이지에 변경이 생기면) 인덱스 페이지 역시 변경이 됩니다. 데이터 페이지에 추가되는 데이터는 빈 공간을 찾아 또는 뒷부분에 자리를 잡는다 하더라도 인덱스 페이지는 인덱스 키값에 의해 순서적으로 위치해야 하므로 자신의 자리를 찾아 비집고 들어가게 됩니다. 이때 인덱스 페이지가 꽉 찬 상태가 아니고 여유 공간이 있다면 기존의 데이터를 뒤로 밀치고 자기 자리를 찾을 수 있지만, 새로운 데이터가 위치해야 될 인덱스 페이지가 꽉 찬 상태라면 비집고 들어갈 공간이 없게 됩니다. 이러한 경우 해당 인덱스 페이지가 절반으로 나뉘게 되어 나누어진 뒷부분의 데이터는 새로운 페이지로 옮겨지게 되고 추가되려던 데이터는 자기 자리를 잡게 됩니다. 이러한 것을 페이지 분열(Page Split) 이라고 합니다.

위 사실로 볼 때 계속적으로 데이터가 추가되거나 변경되게 되면 인덱스 페이지의 데이터들은 순서대로 위치하기 위해 수많은 페이지 분열이 일어나게 되며, 이러한 결과로 INSERT 및 UPDATE 성능이 느려지게 됩니다. 이러한 문제를 방지하기 위해 우리가 할 수 있는 일은 인덱스 페이지를 꽉 차지 않게 여유 공간을 두어 페이지 분열이 일어나지 않도록 하는 것입니다. 이때 사용되는 것이 FILLFACTOR 입니다.

o FILLFACTOR는 인덱스 페이지를 어느 정도 채울 것인지 지정하는 것입니다.

o FILLFACTOR를 이용하여 충분히 인덱스 페이지를 비워둠으로써 페이지 분열을 막아 INSERT 및 UPDATE 성능이 향상되게 됩니다.

o INSERT나 UPDATE가 이루어지지 않는 경우하면 굳이 FILLTACTOR를 사용할 필요가 없습니다. 필요가 없다기보다 사용하지 않는 것이 더 좋습니다. 되도록 데이터가 서로 서로 붙어 있어야 빨리 읽을 수 있으니까요.

그러므로 FILLFACTOR를 지정할 때는 테이블의 용도를 정확히 파악하고 있어야 합니다.

※ 인덱스 페이지에는 Leaf Level과 Non-leaf Level이 있습니다. FILLFACTOR만 사용하면 Leaf Level 인덱스 페이지의 페이지 분열을 막는 역할만을 하게됩니다. 만일 Non-Leaf Level에도 적용하고자 한다면 PAD_INDEX와 같이 사용해야 합니다.

다음은 FILLFACTOR에 대한 몇가지 정보입니다.

o FILLFACTOR 값은 1 부터 100까지 입니다.

o 별도의 FILLFACTOR를 지정하지 않으면 기본값 0 이 설정되어 Leaf Level 인덱스 페이지는 100% 꽉 차게 됩니다.

o 원한다면 sp_configure 시스템 저장 프로시저를 사용하여 기본값을 변경 할 수 있습니다.

o FILLFACTOR 값은 어느 정도 인덱스 페이지를 비울 것인가가 아니고 어느 정도 채울 것인가를 지정하는 것입니다.

o 처음 지정한 FILLFACTOR는 계속 유지되는 것이 아닙니다. 비워두었던 인덱스 페이지도 데이터가 계속 추가됨에 따라 꽉 차게되면 역시 페이지 분열은 이러나게 됩니다. 이런 이유로 매일 새벽 스케줄을 이용하여 적당한 FILLFACTOR값을 이용하여 인덱스를 다시 만들어 주는 경우가 있습니다.

기억하세요! FILLFACTOR는 FILLFACTOR를 이용하여 인덱스를 처음 만들어 주거나 다시 만들어 줄때만 효과가 있다는 것을.

[예제1]

CREATE INDEX idx1 ON Customer(cust_id)

WITH FILLFACTOR = 70

o FILLFACTOR값을 70으로 지정했으므로 Leaf Level 인덱스 페이지는 70% 만 채워지고 30%는 비워두어 페이지 분열을 막아줍니다.

[예제2]

CREATE INDEX idx1 ON Customer(cust_id)

WITH PAD_INDEX, FILLFACTOR = 70

o PAD_INDEX를 사용하였으므로 Leaf Level 뿐만 아니고 Non-leaf Level 인덱스 페이지고 FILFACTOR값을 적용받게 됩니다.

만일 PAD_INDEX를 사용하지 않으면 Non-leaf Level 인덱스 페이지는 꽉 차지는 않고 한 개의 데이터가 들어올 수 있는 공간을 남겨두게 됩니다.

제 12 장 : 뷰(View)

1. 뷰(View)란 무엇인가?

다음의 가정을 세우고 시작하겠습니다.

o 인사관리 시스템을 위한 [직원] 테이블이 있습니다.

o [직원] 테이블에는 직원들의 급여 정보가 들어 있습니다.

o 직원들의 급여정보는 Confidential 이기 때문에 인사팀 외에는 보아서는 안됩니다.

o 총무팀에서 직원관리를 위하여 직원들의 신상정보를 요구해 왔습니다.

o 하지만 총무팀에도 급여정보는 제공할 수가 없습니다.

위 경우에 별도의 [직원2] 테이블을 만들어 총무팀에서 필요로하는 정보만 [직원] 테이블로부터 가져와 기록하여 제공해도 되지만 별도의 테이블을 만들지 않고 [직원] 테이블에서 총무팀에 제공할 컬럼만을 선택하여 가상의 테이블을 만들어 제공 할 수 있습니다. 이렇게 되면 총무팀에서는 만들어진 가상의 테이블을 사용하여 원하는 작업을 할 수 있고, 인사팀은 급여정보가 유출되는 문제를 막을 수 있게 됩니다. 이때 사용되는 가상의 테이블을 뷰(View)라고 합니다.

즉, 뷰(View)란 하나 또는 그 이상의 테이블로부터 생성된, 하지만 물리적으로 존재하지 않는 가상의 논리적인 테이블입니다. 하나의 테이블에서만 SELECT 되어진 결과를 가지고 뷰를 만들 수도 있고, 여러 개의 테이블을 조인(JOIN)하여 SELECT한 결과를 가지고 뷰를 만들 수 있습니다. 사용자(End User) 입장에서는 뷰를 일반적인 테이블과 똑같은 방법으로 사용하게 되므로, 이 뷰가 어떻게 만들어 졌는지 알지 못합니다.

지금까지의 내용으로 보아도 우리는 뷰의 장점을 다음과 같이 이야기 할 수 있습니다.

o 보안성 : 중요한 컬럼(급여정보 처럼)을 숨길 수 있어 보안을 유지 할 수 있습니다.

o 편의성 : 여러 개의 테이블 사용을 위해 복잡한 조인(Join)이 사용되었다 하더라도 사용자는 이를 몰라도 됩니다.

o 간결성 : 뷰를 사용하면 Application을 개발할 때 복잡한 쿼리문을 숨길 수 있어 소스가 간결해집니다.

이외에도 뷰를 사용하다보면 많은 장점들을 경험할 수 있습니다.

2. 뷰(View)를 만드는 방법(CREATE VIEW)

다음의 Employee 테이블의 구조와 Employee 테이블에서 몇몇 컬럼을 가져오는 쿼리문입니다.

CREATE TABLE Employee

(

emp_code char(3) NOT NULL,

emp_name char(10) NOT NULL,

emp_address varchar(70) NULL,

emp_age int,

emp_salary money

)

GO

SELECT emp_code, emp_name, emp_address

FROM Employee

다음과 같이 위 SELECT 퀴리문에 CREATE VIEW를 추가함으로써 뷰(View)를 만들 수 있습니다.

CREATE VIEW vw_Employee

AS

SELECT emp_code, emp_name, emp_address

FROM Employee

이제 vw_Employee 뷰가 만들어 졌으므로 사용자는 이 뷰를 일반 테이블과 마찬가지 방법으로 사용 할 수 있습니다.

SELECT * FROM vw_Employee

o 수행 결과는 맨 처음의 SELECT 문과 같습니다.

o 이상태에서 실제 테이블 Employee에 5개의 Row가 추가 되었다면 vw_Employee 역시 똑같이 5개의 Row를 갖고 있게 됩니다. 단지 컬럼중에 뷰를 만들 때 선언한 컬럼만을 가지고 있다는 차이뿐입니다.

3. 뷰(View)의 특징 몇가지

1) 테이블에서 컬럼이 삭제되면?

다음에 포함된 두 SELECT 문은 결과가 다릅니다.

ALTER TABLE Employee

DROP COLUMN emp_age

GO

SELECT * FROM vw_Employee -- 1)

ALTER TABLE Employee

DROP COLUMN emp_address

GO

SELECT * FROM vw_Employee -- 2)

o 처음에 테이블에서 제거된 컬럼 emp_age는 vw_Employee에 포함되지 않는 컬럼이므로 1)번 SELECT문은 정상적으로 수행됩니다.

o 두 번째 제거된 컬럼 emp_address는 vw_Employee 선언시 포함된 컬럼이므로 2)번 SELECT문은 다음의 오류를 발생시킵니다.

서버: 메시지 207, 수준 16, 상태 3, 프로시저 vw_employee, 줄 3

열 이름 'emp_address'이(가) 잘못되었습니다.

서버: 메시지 4413, 수준 16, 상태 1, 줄 1

바인딩 오류로 인해 뷰 또는 함수 'vw_Employee'을(를) 사용할 수 없습니다.

2) 테이블에 컬럼이 추가되면?

다음과 같이 MyTable 이 구성되어 있고 이 테이블의 모든 내용을 SELECT 해오는 vw_MyTable을 만들었습니다.

CREATE TABLE MyTable

(

col1 CHAR(2),

col2 CHAR(3),

col3 CHAR(1)

)

GO

CREATE VIEW vw_MyTable

AS

SELECT * FROM MyTable

SELECT * FROM MyTable --1)

SELECT * FROM vw_MyTable --2)

o 두 SELECT문의 결과는 의심할 여지없이 동일한 결과를 갖습니다.

이 상태에서 다음과 같이 MyTable에 컬럼을 추가 했습니다.

ALTER TABLE MyTable

ADD col4 CHAR(2)

GO

SELECT * FROM MyTable --1)

SELECT * FROM vw_MyTable --2)

o 1)번 SELECT문은 당연히 추가된 컬럼 col4를 포함한 결과를 보여줍니다.

o 뷰를 선언할 때 모든 컬럼을 포함하라고 SELECT * 했으므로 2)번 SELECT문도 추가된 행이 보일것 같습니다. 하지만!

o 2)번의 결과에는 추가된 컬럼 col4 가 포함되지 않습니다.

o 뷰를 만들때 SELECT * 이라고 했어도 그 당시에 존재하는 컬럼의 정보가 시스템 테이블(syscolumns)에 기록되어 이를 이용해 뷰의 결과가 보여지므로 이후에 추가된 컬럼은 뷰에 포함되지 않게 됩니다.

o 이 사항을 잘 몰라 뷰를 사용할 때 오류를 범하는 경우가 있습니다.

3) 뷰를 사용하면 속도가 빨라진다?

물론 SQL Server 2000에서 제공하는 새로운 기능인 뷰에 인덱스를 만들어주면(많은 제약이 있지만) 일반적인 쿼리문 수행보다 속도가 빨라집니다. 하지만 일반적으로 뷰는 속도 향상보다는 앞 강좌에서 이야기 되었던 장점을 위해 사용됩니다. 사용자가 뷰로부터 SELECT하게 되면 그 순간 SQL Server는 뷰른 만들 때 선언한 쿼리문을 읽어와(syscomments 에서) 그 쿼리문을 일반 쿼리문과 같은 형식으로 수행하므로 속도에는 차이가 없게됩니다.

4. 뷰(View)를 통한 데이터 업데이트

물론 View는 데이터를 업데이트하기 위한 목적보다는 편리하게 검색하기 위한 목적으로 사용됩니다. 하지만 분명 View를 통하여 우리는 실제 테이블의 데이터를 수정할 수 있습니다(View가 수정되는 것이 아니고 실제 데이터가 변경되어 View에는 변경된 내용이 보이는 것입니다).

View를 통해서 테이블의 데이터를 변경하는 것은 다음과 같은 몇가지 제약이 있습니다.

o 동시에 하나의 테이블(View를 구성하는 실제 테이블)만을 변경 할 수 있습니다.

o 계산된 컬럼은 변경할 수 없습니다.

o View를 만들때 WHERE절을 이용하여 선택에 제한을 두었다면 이 제한을 벗어나는 값으로는 변경을 할 수 없습니다.

다음의 예제를 보면서 몇 가지 경우를 설명 드리도록 하겠습니다.

CREATE TABLE Employee

(

emp_code char(05),

emp_name char(10),

dept_code char(05)

)

GO

CREATE TABLE Department

(

dept_code char(05),

dept_name char(10)

)

GO

CREATE VIEW vw_Employee1 --1)

AS

SELECT emp_code, emp_name

FROM Employee

GO

CREATE VIEW vw_Employee2 --2)

AS

SELECT e.emp_code, e.emp_name, e.dept_code, d.dept_name

FROM Employee e

JOIN Department d ON e.dept_code = d.dept_code

GO

o 1)에서는 Employee 테이블 만을 참조하는 뷰를 만들었습니다.

o 2)에서는 Employee 테이블과 Department 테이블을 참조하는(조인으로 연결된) 뷰를 만들었습니다.

[예제1]

UPDATE vw_Employee1 SET emp_name = '이장래' -- 1)

WHERE emp_code = 'EM103'

GO

UPDATE vw_Employee2 SET emp_name = '이장래' -- 2)

WHERE emp_code = 'EM103'

GO

UPDATE vw_Employee2 SET dept_name = '개발팀' --3)

WHERE dept_code = 'DT001'

GO

o 위 모든 경우는 뷰를 통하여 테이블의 데이터를 변경할 수 있는 예입니다.

o 변경의 대상이 되는 실제 테이블이 1), 2)의 경우는 Employee, 3)의 경우는 Department 하나뿐이기 때문입니다.

[예제2]

UPDATE vw_Employee2 SET emp_name = '한국인', dept_name = '인사팀'

WHERE emp_code = 'EM103'

GO

o emp_name을 변경하기 위해서는 Employee테이블이, dept_name을 변경하기 위해서는 Department 테이블이 실제 변경되어야할 테이블입니다.

o 동시에 두 테이블이 변경되어야 하므로 위 예제는 오류를 발생시킵니다.

5. 뷰(View)에 인덱스 만들기

제가 처음 SQL Server 2000을 접하고 놀란 이유 중의 하나가 뷰에 인덱스를 걸 수 있다는 사실입니다. 그전에는 뷰의 편리함을 알았지만 이 뷰라는 것이 성능에는 별 효과가 없어서 항상 불만을 품고 있었습니다. SQL Server 2000은 과감히 이러한 불만을 없애주었습니다. 물론 몇가지 제약이 있기는 하지만 이제 우리는 뷰에 인덱스를 만들어 성능 향상을 볼 수 있습니다.

다음의 예제를 보면서 몇 가지 제약과 함께 부에 인덱스를 만드는 방법을 알아보도록 하겠습니다.

1) SCHEMABINDING 옵션

뷰에 인덱스를 만들 수 있다는 사실에 너무 기뻐 다음과 같이 앞에서 다루었던 뷰에 인덱스를 걸어보고자 했습니다.

CREATE VIEW vw_Employee2

AS

SELECT e.emp_code, e.emp_name, e.dept_code, d.dept_name

FROM Employee e

JOIN Department d ON e.dept_code = d.dept_code

GO

CREATE INDEX idx1 ON vw_Employee2(emp_code)

하지만 우리는 다음과 같은 오류를 보게됩니다.

서버: 메시지 1939, 수준 16, 상태 1, 줄 1

'vw_Employee2' 뷰는 스키마 바운드 뷰가 아니므로 이 뷰에 인덱스을(를) 만들 수 없습니다.

SCHEMABOUND 뷰가 아니기에 인덱스를 만들 수 없다고 합니다. 즉 인덱스를 만들 수 있는 뷰는 다음과 같이 SCHEMABOUND된 뷰에 한해서입니다. 테이블명 앞에 소유자를 붙여야함도 확인하시기 바랍니다.

CREATE VIEW vw_Employee3

WITH SCHEMABINDING -- 여기를 주목

AS

SELECT e.emp_code, e.emp_name, e.dept_code, d.dept_name

FROM dbo.Employee e

JOIN dbo.Department d ON e.dept_code = d.dept_code

GO

2) CLUSTERED UNIQUE 인덱스 필요

하지만 역시 새로 만든 vw_Employee3 뷰에 인덱스를 만들려고 하면 다른 오류를 보게 됩니다. SCHEMABOUND된 뷰임에도 불구하고.

서버: 메시지 1940, 수준 16, 상태 1, 줄 1

'vw_Employee3' 뷰에 인덱스을(를) 만들 수 없습니다. 클러스터된 고유 인덱스가 없습니다.

클러스터드된 인덱스가 없어서 안 된다고 합니다. 즉 뷰에 인덱스를 만들 때는 다음과 같이 제일 먼저 클러스터드된 고유 인덱스를 만들어야 합니다.

CREATE UNIQUE CLUSTERED INDEX idx1 ON vw_Employee3(emp_code)

이제 원한다면 다음과 같은 다른 인덱스도 만들 수 있습니다.

CREATE INDEX idx2 ON vw_Employee3(dept_code)

3) 이외에도

실제적인 테이블을 참조하는 뷰에만 인덱스를 만들 수 있습니다. 뷰를 참조하는 뷰에는 인덱스를 만들 수 없습니다.

제 13 장 : 저장프로시저 (Stored Procedure)

1. 저장 프로시저(Stored Procedure)란

앞에서 배운 뷰(View)는 검색에 사용되는 퀴리문을 미리 저장해두어 이를 쉽게 호출하여 사용함으로써 사용자의 편의를 준다고 했습니다. 저장 프로시저도 이와 비슷합니다. 단 저장 프로시저는 검색뿐만이 아니라 여타의 처리, 즉 Update와 Insert를 포함한 처리를 할 수 있습니다. 여러 가지 퀴리문을 포함하여 일괄 처리하는 배치(Batch)를 아실 겁니다.(사실 강좌에서는 언급한 적이 없네요.) 이러한 배치가 하는 작업을 저장 프로시저는 하게 됩니다. 하지만 배치보다 많은 장점을 지니고 있습니다. 저장 프로시저의 장점을 나열한다면 다음과 같습니다.

o 저장 프로시저에 사용된 모든 구문이 미리 분석되어 최적화된 후 처음 수행시 메모리에 올려져 이후에 사용 될 때는 메모리에 올려진 내용이 수행되므로 속도가 월등히 빠릅니다.

o 복잡한 퀴리문을 네트워크를 통하여 서버로 보낼 필요가 없이, 단지 저장 프로시저를 호출하는 간단한 내용만 서버로 전달되므로 네트워크 트래픽이 감소됩니다.

o 특정 테이블에 대한 권한이 없는 사용자 계정에 저장 프로시저를 수행 할 수 있는 권한을 주어 필요한 작업을 할 수 있게 할 수 있으므로 보안성을 높일 수 있습니다.

o 특정 기능을 수행하는 저장 프로시저를 만들어 두면 여러 응용프로그램에서 이를 활용 할 수 있습니다. 즉, 특정처리를 위한 모듈화작업이 가능합니다. 모듈화가 되어 있으므로 응용프로그램 전체의 수정 없이 해당 저장 프로시저만을 수정하여 원하는 기능 구현을 할 수 있습니다.

저장 프로시저의 사용 예를 간단히 들어보도록 하겠습니다. 예를 들어 다음과 같이 급여의 13%를 보너스로 지급하기위한 내역을 조회하는 저장 프로시저 Calc_Bonus 가 있다고 하겠습니다.(저장 프로시저 만드는 방법은 다시 언급하겠습니다.)

CREATE PROC Calc_Bonus

AS

SELECT emp_code, emp_name, salary, CONVERT(int, salary * 0.13) AS bonus

FROM Employee

많은 회계 프로그램에서는 Calc_Bonus를 호출하여 급여 작업을 할 것입니다. 보너스를 20%로 조정한다면 위와 같이 Calc_Bonus의 계산 부분만을 '* 0.20'으로 고치면 되고, Calc_Bonus를 호출하는 실제 응용 프로그램은 전혀 수정할 필요가 없습니다. 예가 좀 그런가요?

2. 저장 프로시저의 종류

o 시스템 저장 프로시저 : sp_help, sp_who 처럼 'sp_'로 시작되는 프로시저로 SQL Server에의해 기본적으로 제공되는 프로시저입니다. 대부분 master 데이터베이스에 있으며, msdb 또는 distribution 데이터베이스에 있습니다. 사용자 정의 프로시저도 'sp_'를 붙여 만들 수 있으나 시스템 저장 프로시저와 구별이 힘들어지므로 'sp_' 사용은 피하는 것이 좋습니다.

o 사용자 저장 프로시저 : 사용자가 추가로 만든 일반적인 저장 프로시저입니다. 바로 우리가 만들 저장 프로시저입니다.

o 확장 저장 프로시저 : 'xp_'로 시작되며 DLL로 만들어진 외부 함수들이라 생각하시면 됩니다. SQL Server의 기능만을 가지고는 만들수 없는 기능을 구현하기 위해 C++ 과 같은 개발 툴로 만들어진 DLL들 입니다.

o 원격 저장프로시저 : 현재의 SQL Server가 아닌 다른 SQL Server의 저장 프로시저를 호출 할 수 있습니다. 이렇듯 원격으로 호출한다 하여 원격 저장 프로시저라 합니다.

3. 저장 프로시저 만들기

저장프로시저는 수행하려는 배치의 앞부분에 CREATE PROCEDURE를 붙이는 과정으로 만들 수 있습니다. 물론 CREATE PROCEDURE를 CREATE PROC로 줄여 사용 할 수도 있습니다.

앞에서 사용된 Calc_Bonus를 보면 다음과 같이 일반적인 SELECT문 앞에 CREATE PROC를 사용(진하게 표시한 부분)하여 저장 프로시저를 만들었음을 알 수 있습니다.

CREATE PROC Calc_Bonus

AS

SELECT emp_code, emp_name, salary, CONVERT(int, salary * 0.13) AS bonus

FROM Employee

이와 같이 우선 배치가 원하는 기능을 수행하는지 충분히 확인하고 이상이 없으면 CREATE PROC를 앞에 붙여 저장 프로시저를 만드는 것리 오류를 줄이면서 효과적으로 저장 프로시저를 만들 수 있는 방법입니다.

4. 저장 프로시저의 형태

1) 매개변수 없는 저장 프로시저

매개변수(Input or Output)가 없이 단순한 검색만을(또는 처리를)하는 저장 프로시저를 가리킵니다. 앞에서 간단히 다루어본 Calc_Bonus 저장 프로시저는 매개변수를 받지 않고 단순한 결과만을 보여주고 있으므로 이에 속한다 할 수 있겠습니다. 다음의 예는 Books Online에서 가져온 것입니다.

USE pubs

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'au_info_all' AND type = 'P')

DROP PROCEDURE au_info_all

GO

CREATE PROCEDURE au_info_all

AS

SELECT au_lname, au_fname, title, pub_name

FROM authors a INNER JOIN titleauthor ta

ON a.au_id = ta.au_id INNER JOIN titles t

ON t.title_id = ta.title_id INNER JOIN publishers p

ON t.pub_id = p.pub_id

GO

o 저장 프로시저를 만들기전 이미 있는 저장 프로시저를 확인하여 지우는 부분이 앞에 있습니다. 이 부분을 참고하시면 많은 도움이 될 것입니다.

o 저장 프로시저도 하나의 개체이므로 sysobjects 테이블 안에 기록되어 있습니다. 이 정보를 이용하여 기존 존재 유무를 확인합니다.

o 저장 프로시저 삭제는 DROP PROCEDURE 를 이용합니다.

o au_info_all만 호출하면 복잡한 JOIN문이 포함된 검색을 쉽게 할 수 있게 됩니다.

아무 매개변수가 없는 au_info_all 저장 프로시저는 다음과 같이 저장 프로시저를 호출 할 수 있습니다.

EXEC au_info_all

o EXEC 대신에 EXECUTE를 사용하셔도 됩니다.

2) 입력 매개변수를 갖는 프로시저

특정 매개변수를 받아 처리하는 저장 프로시저를 만들 수 있습니다. 다음의 예 역시 Books Online에서 가져온 것임을 밝힙니다.

CREATE PROCEDURE au_info

@lastname varchar(40),

@firstname varchar(20)

AS

SELECT au_lname, au_fname, title, pub_name

FROM authors a INNER JOIN titleauthor ta

ON a.au_id = ta.au_id INNER JOIN titles t

ON t.title_id = ta.title_id INNER JOIN publishers p

ON t.pub_id = p.pub_id

WHERE au_fname = @firstname

AND au_lname = @lastname

GO

o lastname과 firstname을 매개변수로 받아들여 조건문(WHERE절)에 사용하고 있습니다.

o 변수앞에는 이와 같이 @를 붙여 사용합니다.

o 사용된 JOIN문이 어렵게 느껴진다면 무시하시기 바랍니다. 단지 매개변수의 사용 방법만 확인하시면 됩니다.

입력 매개변수가 있는 저장 프로시저는 다음과 같이 호출합니다.

EXEC au_info 'Dull', 'Ann'

3) 출력 매개변수를 갖는 프로시저

검색결과 또는 연산 결과를 호출한 쪽에 넘겨주는 저장 프로시저를 만들 수 있습니다. 저장 프로시저를 만들거나 이를 호출할 때 OUTPUT을 사용하게 됩니다. OUTPUT 매개변수가 사용된 아주 간단한 예를 보도록 하겠습니다.

CREATE PROC MyCalc_Sample

@val01 int = 0,

@val02 int = 0,

@val03 int OUTPUT

AS

SET @val03 = @val01 + @val02

o 두개의 입력 매개변수와 하나의 출력 매개변수를 사용하였습니다.

o @val01 int = 0 과 같이 입력 매개변수를 선언할 때 매개변수가 생랙될 경우 처리할 기본 값을 정의 할 수 있습니다.

o 두 변수의 합을 갖는 @val03이 OUTPUT으로 선언되었으므로 이 값을 외부에서 호출한 곳에서 사용 할 수 있습니다.

다음은 위에서 만든 MyCalc_Sample 저장 프로시저는 사용한 예입니다.

DECLARE @sum int

EXEC MyCalc_Sample 1, 2, @sum OUTPUT

PRINT @sum

o @sum이 계산된 OUTPUT결과를 받게되므로 3이라는 값을 갖게 됩니다.

o 그래서 마지막 PRINT문은 3을 표시하게 됩니다.

다음의 예에서 @sum이 갖게 되는 값은 얼마일까요?

DECLARE @sum int

EXEC MyCalc_Sample @val01 = 100 , @val03 = @sum OUTPUT

PRINT @sum

o 입력 매개변수 하나를 생략하였습니다.

o 입력 매개변수를 생략할 때는 위와 같이 변수명을 일일이 적어야 합니다.

o 임력 매개변수 @val02가 생략되었으므로 기본값 0이 @val02에 대입되며 100과 0의 합인 100이 @sum에 전달됩니다.

5. 저장 프로시저 변경(ALTER PROCEDURE)

전에 언급한적 있지요? CREATE에 의해 만들어진 것은 DROP에 의해 제거되고 ALTER에 의해 변경되어 진다고. 기억 안 나시나요?(언급한 적이 없나???) 이미 만들어진 저장 프로시저는 ALTER PROCEDURE문을 이용하여 수정 할 수 있습니다. 물론 DROP을 이용하여 제거 한 후 다시 만들 수 있지만 이렇게 되면 그 저장 프로시저에 관련된 모든 권한을 다시 설정해야 합니다. 하지만 ALTER PROCEDURE문을 저장 프로시저의 내용만 바꿀 뿐 다른 것은 변화가 없습니다.

6. 저장 프로시저 암호화(WITH ENCRYPTION)

괜히 언급을 하나 싶기도 하네요. 우리가 만들었던 저장 프로시저나 시스템 저장 프로시저는 sp_helptext 저장 프로시저를 이용하여 소스를 확인 할 수 있습니다. 만일 이 소스를 확인하지 못하도록 숨기고 싶다면 이를 암호화 할 수 있습니다. 'WITH ENCRYPTION' 옵션을 이용하면 되는데, 한번 암호화된 소스는 원상 복구시킬 방법이 없으므로 소스 자체(스크립트)를 잘 보관해두어야 합니다. 주의하시기 바랍니다.

제 14 장 : 사용자 정의 함수(User Defined Function)

1. 사용자 정의 함수란

저장 프로시저(Stored Procedure)가 어떠한 처리를 위해 수행되는 것과는 달리 함수는 짐작되다시피 어떤 특정한 값을 사용자에게 넘겨주는(리턴) 기능을 갖습니다. 예를 들어 GetDate() 함수는 현재의 날짜를 리턴해줍니다. SQL Server 2000에서는 사용자가 자신만의 함수를 만들 수 있는 기능을 제공합니다. 이것이 사용자 정의 함수(User Defined Function)입니다. 물론 저장 프로시저도 OUTPUT 매개변수를 이용하여 값을 넘겨줄 수 있지만 값을 얻기 위해 저장 프로시저를 사용하기는 적당하지 않은 부분이 많습니다. 사용자 정의함수는 특정한 값만을 리턴하는게 아니고 테이블을 리턴해주기도 합니다. 또한 쿼리문의 여러 부분에서 사용될 수 있어 업무에 많은 도움을 줍니다. 이 부분에 대해서는 나중에 확인하도록 하겠습니다.

사용자 정의 함수는 CREATE FUNCTION을 이용해서 만들고 DROP FUNCTION을 이용해서 제거되며 ALTER FUNCTION을 이용해서 변경되어집니다. CREATE FUNCTION은 만들어지는 사용자 정의 함수의 종류에따라 여러가지 방법이 있습니다. 우선 사용자 정의 함수의 종류를 알아보고 각 종류별로 만드는 방법과 사용예를 살펴보도록 하겠습니다.

사용자 정의 함수는 다음과 같이 분류할 수 있습니다.

o 스칼라 반환 사용자 정의 함수 (Scalar Functions)

o 인라인 테이블 반환 함수 (In-Line Table-valued Functions)

o 다중 문 테이블 반환 함수 (Multi-Statement Table-valued Functions)

1) 스칼라 반환 사용자 정의 함수(Scalar Functions)

GetDate()함수의 경우 리턴하는 값은 현재의 날짜입니다. 이렇듯 특정 값만을 리턴하는 함수를 Scalar Function 이라고 합니다. 만일 두 값의 합을 구하여 리턴해주는 사용자 정의 함수를 구현한다면 다음과 같이 만들 수 있습니다.

[예제1]

CREATE FUNCTION fn_sum (@val01 int, @val02 int) -- 1)

RETURNS int -- 2)

AS

BEGIN

RETURN (@val01 + @val02) -- 3)

END

GO

SELECT dbo.fn_sum(100, 200) -- 4)

o '--' 는 수행과는 아무 상관없는 Comments 를 위해 사용됩니다.

o 1)에서 CREATE FUNCTION을 사용했습니다. 이때 만들어지는 함수의 이름은 fn_sum이며, @val01과 @val02 두개의 int 타입 매개변수가 사용됨을 알 수 있습니다.

o 2)에서 fn_sum 함수가 int 타입의 값을 리턴한다고 지정하고 있습니다.

o 3)에서 함수에 전달된 @val01과 @val02의 값을 더한 결과를 RETURN하고 있습니다.

o 4)의 SELECT 결과는 100과 200의 합인 300이 됩니다. 사용자 정의 함수를 호출 할때는 이처럼 '소유자.함수이름(매개번수)' 형태를 취해야 합니다. 수유자가 dbo라 하더라도 생략할 수 없습니다.

[예제2]

CREATE FUNCTION fn_DateFormat(@indate datetime, @separator char(1))

RETURNS Nchar(20)

AS

BEGIN

RETURN

CONVERT(Nvarchar(20), datepart(mm,@indate))

+ @separator

+ CONVERT(Nvarchar(20), datepart(dd, @indate))

+ @separator

+ CONVERT(Nvarchar(20), datepart(yy, @indate))

END

GO

SELECT dbo.fn_DateFormat(GetDate(),'/')

o 날짜를 지정한 문자로 월,일,년을 분리하여 보시해주는 사용자 정의 함수의 예입니다.

※ 사용자 정의함수 호출 방법

스칼라 사용자 정의 함수를 호출할 때는 적어도 다음과 같이 두 부분(소유자.함수명)으로 된 이름을 제공해야 합니다.

SELECT *, MyUser.MyScalarFunction()

FROM MyTable

테이블 값을 리턴하는 함수는 다음과 같이 한 부분으로 된 이름을 사용하여 호출할 수 있습니다.

SELECT *

FROM MyTableFunction()

그러나 테이블을 반환하는 SQL Server 기본 제공 함수를 사용할 때는 함수 이름에 접두어 ::를 추가해야 합니다.

SELECT * FROM ::fn_helpcollations()

2) 인라인 테이블 반환 함수 (In-Line Table-valued Functions)

인라인 테이블 변환 함수는 함수 본문이 하나의 SELECT 문으로 정의된 테이블 반환 함수입니다.인라인 사용자 정의 함수는 다음 규칙을 따릅니다.

o RETURNS 절에는 키워드 table만 포함됩니다. 반환 변수 형식은 RETURN 절에 있는 SELECT 문의 결과 집합에서 설정되므로 사용자가 정의하지 않아도 됩니다.

o BEGIN과 END로 구분되는 function_body는 없습니다.

o RETURN 절에는 하나의 SELECT 문이 괄호 안에 포함됩니다. 함수에서 반환하는 테이블은 SELECT 문의 결과 집합으로 구성됩니다. 인라인 함수에서 사용되는 SELECT 문은 뷰에서 사용되는 SELECT 문과 같은 제한을 받습니다.

[예제1]

USE pubs

GO

CREATE FUNCTION SalesByStore (@storeid varchar(30))

RETURNS TABLE

AS

RETURN (SELECT title, qty

FROM sales s, titles t

WHERE s.stor_id = @storeid and t.title_id = s.title_id)

o 진하게 표시된 부분을 자세히 살펴보시기 바랍니다.

o 다른 사용자 정의 함수와는 달리 BEGIN...END문이 없습니다.

o RETURN 문의 괄호 안에 하나의 SELECT문이 포함되어 있습니다.

o 다음의 뷰(VIEW)를 보면 위 SalesByStore 함수와 같은 내용을 표시해 주지만 여러가지 경우에 따른 변화된 내용을 보여주지는 못합니다. 이런 뷰의 단점을 인라인 테이블 반환 함수는 해결해 줍니다.

USE pubs

GO

CREATE VIEW vw_Sales

AS

SELECT title, qty

FROM sales s, titles t

WHERE t.title_id = s.title_id

다음은 위의 사용자 정의 함수와 뷰를 이용하여 검색하는 예입니다. 처음의 사용자 정의 함수는 Stor_id가 7067인 것만을 표시하지만 뷰는 전체를 다 표시하게 됩니다.

SELECT * FROM SalesByStore(7067)

SELECT * FROM vw_Sales

3) 다중 문 테이블 반환 함수 (Multi-Statement Table-valued Functions)

다중 문 테이블 변환 함수는 뷰(VIEW)와 저장 프로시저(Stored Procedure)의 복합형태로 생각하시면 됩니다. 즉, 뷰처럼 테이블을 반환해주고, 저장 프로시저처럼 여러 가지 처리를 함수 내부에서 하게 됩니다. 이런 이유로 함수를 만드는 형태가 꽤나 복잡합니다.

table을 반환하는 사용자 정의 함수에서는 다음을 수행합니다.

o RETURNS 절에서 함수가 반환한 테이블에 로컬 반환 변수 이름을 정의합니다.

o RETURNS 절에서 테이블 형식도 정의합니다. 로컬 반환 변수 이름의 범위는 함수 내에서 로컬입니다.

o 함수 본문에 있는 Transact-SQL 문에서 행을 작성하여 RETURNS 절에서 정의된 반환 변수에 삽입합니다.

o RETURN 문이 실행될 때 변수에 삽입된 행은 함수의 테이블 형식 출력으로 반환됩니다. RETURN 문에서는 인수를 사용할 수 없습니다.

o 테이블을 반환하는 함수에 있는 어떠한 Transact-SQL 문도 사용자에게 직접 결과 집합을 반환할 수 없습니다.

o 함수에서 사용자에게 반환할 수 있는 유일한 정보는 함수에서 반환된 table입니다.

두 가지 예제를 보면서 만드는 방법을 확인하도록 하겠습니다. 첫번째 예제는 MOC 교재에 수록된 것이며, 두 번째 예제는 Books Online에 수록된 예제입니다.

[예제1]

USE Northwind

GO

CREATE FUNCTION fn_Employees (@length nvarchar(9))

RETURNS @fn_Employees table

(EmployeeID int PRIMARY KEY NOT NULL,

[Employee Name] nvarchar(61) NOT NULL)

AS

BEGIN

IF @length = 'ShortName'

INSERT @fn_Employees SELECT EmployeeID, LastName

FROM Employees

ELSE IF @length = 'LongName'

INSERT @fn_Employees SELECT EmployeeID,

(FirstName + ' ' + LastName) FROM Employees

RETURN

END

GO

SELECT * FROM dbo.fn_Employees('LongName')

-- 또는

SELECT * FROM dbo.fn_Employees('ShortName')

o 진하게 표시된 부분을 자세히 살펴보시기 바랍니다.

o 만드는 방법이 다소 복잡하게 보입니다.

o RETURNS 문에 @fn_Employees 테이블이 컬럼 구조와 함께 선언되어 있습니다.

o 이 @fn_Employees 테이블에 SELECT결과를 INSERT해주고 있습니다.

o @fn_Employees 테이블의 내용이 실제 함수가 리턴해주는 결과입니다.

[예제2]

CREATE FUNCTION LargeOrderShippers (@FreightParm money)

RETURNS @OrderShipperTab TABLE

(

ShipperID int,

ShipperName nvarchar(80),

OrderID int,

ShippedDate datetime,

Freight money

)

AS

BEGIN

INSERT @OrderShipperTab

SELECT S.ShipperID, S.CompanyName,

O.OrderID, O.ShippedDate, O.Freight

FROM Shippers AS S INNER JOIN Orders AS O

ON S.ShipperID = O.ShipVia

WHERE O.Freight > @FreightParm

RETURN

END

o [예제1]과 형식이 똑 같습니다.

o 다른 것은 함수 이름과 테이블 구조와 어떤 결과가 이 테이블에 기록되어 리턴되는가 하는 것뿐입니다.

4) 기타

사용자 정의 함수는 SQL Server 2000에서 처음 선보이는 기능이므로 이전 버전 사용자에게는 꽤 낯설게 보일 수 있지만 이 기능을 활용하면 많은 도움을 받을 수 있습니다. 사용자 정의 함수는 WHERE 절에도 사용이 가능하며 FROM 절에도 사용이 가능합니다. Books Online에 사용자 정의 함수에 대한 많은 설명이 포함되어 있습니다. Books Online의 내용을 꼭 확인하여 보시기 바랍니다.

제 15 장 : 트리거(Trigger)

1. 트리거(Trigger)란

트리거란 특정 테이블의 데이터에 변경이 가해졌을 때 자동으로 수행되는 저장 프로시저라고 할 수 있습니다. 예전에 배웠던 저장 프로시저는 필요할 때마다 사용자가 직접 수행시켜야 했습니다.(물론 SQL Server가 시작될 때 자동으로 수행되게 하는 방법이 있기는 하지만) 하지만 트리거는 이와 달리 테이블의 데이터가 INSERT, UPDATE, DELETE문에 의해 변경 되어질 때 자동으로 수행되므로 이 기능을 이용하며 여러 가지 작업을 할 수 있습니다. 이런 이유로 트리거를 사용자가 직접 수행 할 수는 없습니다.

예를 들어 [사원] 테이블에 새로운 데이터가 들어오면(즉 신입 사원이 들어오면) [급여] 테이블에 새로운 데이터(즉 신입 사원의 급여 정보)를 자동으로 생성하고 싶을 경우 [사원] 테이블에 트리거를 설정하여 구현 할 수 있습니다.

2. INSERTED, DELETED 테이블

트리거를 제대로 사용하기 위해서는 다음의 두 테이블에 대하여 이해하고 있어야 합니다.

o INSERTED 테이블

o DELETED 테이블

위 두 테이블은 물리적으로 존재하는 테이블이 아닙니다. 특정 테이블의 데이터에 변경이 가해졌을 때 트리거를 위해 자동으로 만들어지는 논리적인(실제로 존재하지 않는 가상의) 테이블입니다. 그 이름으로 추측되다시피 INSERTED 테이블은 테이블에 새로운 데이터가 INSERT될 때, DELETED 테이블은 테이블의 데이터가 DELETE될 때 만들어집니다.

그렇다면 왜 UPDATED 테이블이 없냐고요? 테이블의 데이터가 UPDATE 되는 것은 기존의 데이터가 DELETE되면서 새로운 데이터가 INSERT되는 과정으로 보아 DELETED 테이블과 INSERTED 테이블이 동시에 만들어집니다. 즉, DELETED 테이블에는 UPDATE 되기전 내용이 포함되어 있으며, INSERTED 테이블에는 변경된 새로운 내용이 포함되어 있습니다.

3. 트리거 만들기(CREATE TRIGGER)

CREATE TRIGGER문에 의해서 트리거를 만들수 있습니다. 물론 DROP TRIGGER문에 의해서 트리거를 제거 할 수 있으며, ALTER TRIGGER에 의해서 기존의 트리거를 수정 할 수 있습니다.

트리거를 만들때는 다음의 내용을 포함하게 됩니다.

o 트리거의 이름

o 어느 테이블에 대한 것인지 테이블 이름 지정

o INSERT, DELETE, UPDATE중 어느 경우에 수행될 트리거인지 지정(복수 선택 가능)

o 실제 수행될 쿼리문(이 쿼리문 안에서 INSERTED, DELETED 테이블을 이용하게 됩니다)

트리거를 통하여 어떤 작업이든 할 수 있는게 아닙니다. 트리거를 통해 할 수 없는 작업을 몇 가지 나열해 보도록 하겠습니다.

o 데이터베이스 변경(ALTER DATABASE)

o 데이터베이스 생성(CREATE DATABASE)

o 데이터베이스 제거(DROP DATABASE)

o 데이터베이스 복구(RESTORE DATABASE)

o 로그 복구(RESTORE LOG)

o 기타(DISK INIT, DISK RESIZE, LOAD DATABASE, LOAD LOG, RECONFIGURE)

우선 간단한 트리거의 예를 보도록 하겠습니다.

USE Pubs

GO

CREATE TRIGGER trg_Sales_Delete ON Sales

FOR DELETE

AS

IF (SELECT COUNT(*) FROM Deleted) > 1

BEGIN

RAISERROR('You cannot delete more than one row at a time',16,1)

ROLLBACK TRANSACTION

END

o 트리거의 이름은 trg_Sales_Delete 입니다.

o DELETE에 대한 트리거입니다.

o Deleted 테이블의 Row 카운트를 이용하여 몇 개의 Row가 지워졌는지 확인합니다.

o Pubs 데이터베이스의 Sales 테이블이 한번에 두개이상 지워지면 오류를 발생시키도록 했습니다.

o 오류 메시지를 발생시키고 ROLLBACK TRANSACTION을 이용하여 DELETE 트랜잭션을 취소시킵니다.

위 예처럼 트리거 안에서 ROLLBACK TRANSACTION을 수행하면 트리거를 발생시킨 이전의 INSERT, DELETE, UPDATE 자체를 완전히 취소시키게 됨을 기억하시기 바랍니다.

위 트리거가 설정된 상태에서 한번에 여러 개의 Row를 지우려고 하면 다음과 같이 오류가 발생합니다.

서버: 메시지 50000, 수준 16, 상태 1, 프로시저 trg_Sales_Delete, 줄 6

You cannot delete more than one row at a time

4. 트리거의 예

몇 가지 트리거의 예를 들어 보도록 하겠습니다. 기초 강좌이니만큼 복잡한 내용은 다루지 않고 단순히 어떤 원리로 작동하는지 이해하기 위한 단순한 예입니다. Books Online을 보시면 여러 가지 예가 나와 있습니다. 이를 참고하시기 바랍니다.

우선 다음과 같이 두개의 테이블을 만들었습니다.

CREATE TABLE Table1

(

col1 char(05),

col2 int

)

GO

CREATE TABLE Table2

(

col1 char(05),

col2 int

)

GO

[예제1]

INSERT 트리거의 예입니다.

CREATE TRIGGER trg_tbl1_Insert ON Table1 -- 1)

FOR INSERT

AS

INSERT INTO Table2(col1, col2) SELECT col1, col2 FROM Inserted

GO

INSERT INTO Table1 VALUES('AAAAA', 10) -- 2)

INSERT INTO Table1 VALUES('BBBBB', 20) -- 3)

GO

SELECT * FROM Table2 -- 4)

o Table1에 INSERT된 데이터가 그대로 Table2에 INSERT 되도록 하는 트리거입니다.

o 트리거를 만들고 2)와 3)에 의해서 두개의 Row를 Table1에 추가했습니다.

o 4)에서 Table2의 내용을 SELECT해보면 Table1과 같이 두개의 Row를 보게 됩니다.

[예제2]

DELETE 트리거의 예입니다. 현재 Table1에는 col1이 'AAAAA'인 것과 'BBBBB' 인거 두개의 Row가 있습니다.

CREATE TRIGGER trg_tbl1_Delete ON Table1

FOR DELETE

AS

DECLARE @col1 char(05)

SELECT @col1 = col1 FROM Deleted

DELETE FROM Table2 WHERE col1 = @col1

GO

DELETE FROM Table1 WHERE col1 = 'AAAAA' -- 1)

또는

DELETE FROM Table1 -- 2)

또는

DELETE FROM Table1 WHERE col1 = 'AAAAA' -- 3)

DELETE FROM Table1 WHERE col1 = 'BBBBB' -- 4)

GO

SELECT * FROM Table2 -- 5)

o Table1에서 데이터가 DELETE되면 Table2에서도 지워지게 하는 트리거입니다.

o 1) 번이 수행되면 Table2에서도 col1이 'AAAAA" 인 Row가 지워집니다.

o 2) 번이 수행되면 Table1은 다 지워지지만 Table2는 하나의 Row만 지워집니다.

o 3) 번과 4) 번이 같이 수행되어 Table1이 다 지워지면 Table2도 다 지워집니다.

[예제3]

UPDATE 트리거의 예입니다.

CREATE TRIGGER trg_tbl1_Update ON Table1

FOR UPDATE

AS

DECLARE @col1 char(5)

DECLARE @col2 int

SELECT @col1 = col1, @col2 = col2

FROM Inserted

UPDATE Table2 SET col2 = @col2

WHERE col1 = @col1

GO

UPDATE Table1 SET col2 = 30 WHERE col1 = 'AAAAA' -- 1)

GO

SELECT * FROM Table2 -- 2)

o Table1에 UPDATE된 내용이 그대로 Table2에 UPDATE 되도록 하는 트리거입니다.

o 1)에 의해서 col1 이 'AAAAA'인 Row의 col2 가 30으로 바뀌었습니다.

o 2)에서 Table2의 내용을 SELECT해보면 Table1과 같이 바뀐 내용이 보입니다.

o 굳이 @col1, @col2와 같은 변수를 이용하지 않고 JOIN을 이용하여 구현할 수도 있습니다.

[예제4]

UPDATE 트리거에서 특정 컬럼의 변경 여부를 확인하여 처리하는 예입니다.(MOC 교재에 있는 예입니다.)

USE Northwind

GO

CREATE TRIGGER Employee_Update

ON Employees

FOR UPDATE

AS

IF UPDATE(EmployeeID) -- 1)

BEGIN

RAISERROR('Employee ID number cannot be modified.',10,1) -- 2)

ROLLBACK TRAN -- 3)

END

o 1)에서처럼 EmployeeID 컬럼이 UPDATE된 경우에는

o 2)에서처럼 오류 메시지를 보여주고

o 3)에 의해서 처리가 ROLLBACK 됩니다.

이처럼 트리거는 데이터 무결성을 유지하기 위한 방법으로도 사용 할 수 있습니다.

5. INSTEAD OF TRIGGER

지금까지 다루어본 트리거는 INSERT, DELETE, UPDATE가 처리된 후 수행되는 특징이 있습니다.

하지만INSTEAD OF Trigger를 설정하면 트리거만 처리될 뿐 이를 발생시킨 INSERT, DELETE, UPDATE 문은 수행되지 않습니다. FOR INSERT, FOR DELETE, FOR UPDATE대신 FOR INSTEAD OF INSERT, FOR INSTEAD OF DELETE, FOR INSTEAD OF UPDATE로 지정하면 됩니다.

원래 INSTEAD OF 위치에 AFTER를 지정해야 했으나 AFTER는 생략할 수 있으므로 지금까지 생략하고 사용한 것입니다.